Excel VBA Copy Paste

Question

I'm trying to copy and paste a certain range from one worksheet to two other worksheets. This is a snippet of code where it seems to go wrong:

row = ActiveWorkbook.Sheets("SheetX").Cells(Rows.Count, 3).End(xlUp).Row

ws.Range("A1", "J1").Copy
ActiveWorkbook.Sheets("Sheet1").Range("B2", "K2").PasteSpecial xlPasteValues
ActiveWorkbook.Sheets("SheetX").Range(Cells(row, 3),  Cells(row,12)).PasteSpecial xlPasteValues

Since the amount of rows is dynamic "row" holds the row number I want to paste it to.

The problem is that I get a "Application-defined or Object-defined"-error on the last line, where I try to past it to the second worksheet.

score 0 · Accepted Answer · answered Oct 06 '15 at 01:37

When you run Cells(row,3) you are actually calling Cells(row,3) on the ActiveSheet and not on Sheets("SheetX"). Instead you should do this:

row = ActiveWorkbook.Sheets("SheetX").Cells(Rows.Count, 3).End(xlUp).Row

ws.Range("A1", "J1").Copy
ActiveWorkbook.Sheets("Sheet1").Range("B2", "K2").PasteSpecial xlPasteValues
Range(ActiveWorkbook.Sheets("SheetX").Cells(row, 3),  ActiveWorkbook.Sheets("SheetX").Cells(row,12)).PasteSpecial xlPasteValues

Excel VBA Copy Paste

1 Answers1