Problem with code PasteSpecial Excel VBA macro

Question

I'm writing, bc I have a problem with my macro. Below you can find it:

Workbooks.Open FileName:="xxxxx.xlsx"
Worksheets("xxxx").Activate
Dim A As Long
A = Cells(Rows.Count, 1).End(xlUp).Row + 1

ThisWorkbook.Sheets("Tabelle1").Range("I6").Copy (ActiveWorkbook.Sheets("xxx").Cells(A, 1))
ThisWorkbook.Sheets("Tabelle1").Range("D3").Copy (ActiveWorkbook.Sheets("xxx").Cells(A, 2))
ThisWorkbook.Sheets("Tabelle1").Range("A6").Copy (ActiveWorkbook.Sheets("xxx").Cells(A, 3))
ThisWorkbook.Sheets("Tabelle1").Range("A37").Copy (ActiveWorkbook.Sheets("xxx").Cells(A, 4))
ThisWorkbook.Sheets("Tabelle1").Range("D37").Copy (ActiveWorkbook.Sheets("xxx").Cells(A, 5))

And I want to copy this cells from one file to another, but paste only values. Now it's working, but copies everything, also with fr example drop-down list :/ When I try this:

Workbooks.Open FileName:="F:\Departments\PUBLIKACJE_DLA_INNYCH_DZIALOW\OES(WRO)\08_Before After Kaizen\Rejestr B_A Kaizen FxxWr.xlsx"
Worksheets("FCGWr").Activate
Dim A As Long
A = Cells(Rows.Count, 1).End(xlUp).Row + 1

ThisWorkbook.Sheets("Tabelle1").Range("I6").Copy (ActiveWorkbook.Sheets("FCGWr").Cells(A, 1)).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Sheets("Tabelle1").Range("D3").Copy (ActiveWorkbook.Sheets("FCGWr").Cells(A, 2)).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Sheets("Tabelle1").Range("A6").Copy (ActiveWorkbook.Sheets("FCGWr").Cells(A, 3)).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Sheets("Tabelle1").Range("A37").Copy (ActiveWorkbook.Sheets("FCGWr").Cells(A, 4)).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Sheets("Tabelle1").Range("D37").Copy (ActiveWorkbook.Sheets("FCGWr").Cells(A, 5)).PasteSpecial Paste:=xlPasteValues

it shows me every time an error, but I don't know where is it :/ I've tried with Selection, with something like: Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False but still it doesn't work :/

Can you help me ? :) (ofc there are fragments of code, I remember about sub xxx() and end sub :))

Nicholas Hunter Nicholas Hunter · Accepted Answer · 2021-05-02T16:19:34

Your code should look like this.

ThisWorkbook.Sheets("Tabelle1").Range("I6").Copy 
ActiveWorkbook.Sheets("FCGWr").Cells(A, 1).PasteSpecial Paste:=xlPasteValues

Problem with code PasteSpecial Excel VBA macro

1 Answers