0

I would like to copy data from a sheet "Inv_Headers", Column C, from 2nd row until the last row to a sheet "Customers", Column U, from 4th row.

Private Sub Invoice_C()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim lastrow As Long

    Set ws = Worksheets("Inv_Headers")
    Set ws2 = Worksheets("CUSTOMERS")
  
    lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row ' last row in column C
    ws.Range("C2:C" & lastrow).Copy
    ws1.Range("U4").PasteSpecial xlPasteValues
    ws1.Activate
End Sub

My code is giving me

msg '91' - Object variable or With block variable not set.

Community
  • 1
  • 1
Srpic
  • 450
  • 5
  • 13
  • 29
  • 1
    You called your first sheet ws, not ws1, so change the ws1 in: ws1.Range("U4").PasteSpecial xlPasteValues – Absinthe Oct 16 '17 at 13:41
  • @Absinthe oh, damn. Thank you! I haven't noticed. – Srpic Oct 16 '17 at 13:43
  • 1
    Perfect example of why you should use `Option Explicit` for each module, and compile before running. This would immediately raise an error on line `Set ws2=....` – iDevlop Oct 16 '17 at 13:44

2 Answers2

1

Based on check from @Absinthe, I've corrected the typo and here is the correct code:

Private Sub Invoice_C()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim lastrow As Long

    Set ws = Worksheets("Inv_Headers")
    Set ws1 = Worksheets("CUSTOMERS")


        lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row ' last row in column C
        ws.Range("C2:C" & lastrow).Copy
        ws1.Range("U4").PasteSpecial xlPasteValues
        ws1.Activate


End Sub
Srpic
  • 450
  • 5
  • 13
  • 29
0

In addition to Srpic's offering, I can remember not getting this part to work: ws.Range("C2:C" & lastrow).Copy

you can fix it with ws.Range("C2", "C" & lastrow).Copy

If you start typing in Range() you will see that , is an acceptable separator, whereas : for an incomplete range assingment is not.

Private Sub Invoice_C()
Dim ws As Worksheet, ws1 As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Inv_Headers")
Set ws1 = Worksheets("CUSTOMERS")


    lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row ' last row in column C
    ws.Range("C2", "C" & lastrow).Copy
    ws1.Range("U4").PasteSpecial xlPasteValues
    ws1.Activate

End Sub

itChi
  • 642
  • 6
  • 19