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 · Accepted Answer · 2021-05-02 16:19:34Z

0

Your code should look like this.

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

answered May 2, 2021 at 16:19

Nicholas Hunter

1,8561 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with code PasteSpecial Excel VBA macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related