0

While doing the second paste I'm getting an error 1004, can anyone please help, I'm not sure what I'm doing wrong here.

wb1.Sheets(1).Range("A1").Copy 
lr = wb2.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row + 1 
wb2.Sheets(1).Range("B"& lr).PasteSpecial xlPasteValues

wb1.Sheets(1).Range("F1:F5").Copy 
wb2.Sheets(1).Range("C"& lr).Pastespecial xlPasteValues, Transpose:=True
Lynas Con
  • 1
  • 3
  • "lr" gives integer value for the next empty row – Lynas Con Mar 22 '21 at 13:52
  • 1
    [This is a better way to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), but seems unrelated to the RTE 1004. – BigBen Mar 22 '21 at 14:03
  • Start at the bottom and search up, Most likely you do not have data in B2 and when you do `lr = wb2.Range("B1").End(xlDown).Row + 1` you are going to the bottom of the page and trying to paste one row off the bottom of the page. See the link in @BigBen comment. – Scott Craner Mar 22 '21 at 14:05
  • 2
    You also need to specify the sheet in `wb2` for `lr`. `lr=wb2.Sheets(1).` – Darrell H Mar 22 '21 at 14:07
  • i tried the way @BigBen mentioned but it still gives error for this line wb2.Sheets(1).Range("C"& lr).Pastespecial xlPasteValues, Transpose:=True – Lynas Con Mar 22 '21 at 14:11
  • `lr = wb2.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row + 1` – Scott Craner Mar 22 '21 at 14:12
  • @ScottCraner I wrote the exact line before, it seems the error still there. – Lynas Con Mar 22 '21 at 14:16
  • What does `Debug.Print lr` return in the Immediate Window? Are there protected cells on `wb.Sheets(1)`? – BigBen Mar 22 '21 at 14:50

1 Answers1

0

I copy/pasted your code with this addition:

Dim wb1 As Workbook: Set wb1 = ThisWorkbook
Dim wb2 As Workbook: Set wb2 = Workbooks("Book2")
Dim lr As Long

But it worked correctly.

I suggest checking this:

  • walk through the code with F8 and verify what is in the variables
  • make sure that the part you're copying to isn't protected
Bart Wille
  • 1