Global error selecting ranges - Excel VBA

Question

I have an excel workbook with 2 sheets, Sheet1 & Sheet2. I have a table on Sheet1 with 10 individuals. I'm trying to put together a macro that will copy and paste the information from a specific range A2:I2 then copy it to the next empty row on Sheet2

This is my code so far, (it's not finished as i'm trying to build it a step at a time so I can improve my understanding of the code)

Dim NR As Long

NR = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Application.ScreenUpdating = False

Worksheets("Sheet1").Activate
Range("A2:I2").Select
Selection.Copy
Worksheets("Sheet2").Activate
Range("B:I" & NR).Select

I keep getting this error message

Run-Time error "1004" Method "Range" of Object' _Global Failed.

It seems to relate to the last range selection. I don't understand enough to know why it's not selecting the range required.

Any ideas?

Thanks.

Answer 1

As @Vityata said - you're not referencing the range correctly, and as @Harassed said - your NR is looking at the currently active sheet.

You're also using Activate and Select when you don't need to - just tell it which sheet to copy from and which to paste to.

You can rewrite the code as:

Sub Test()

    Dim NR As Long

    NR = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet1").Range("A2:I2").Copy _
        Destination:=Worksheets("Sheet2").Range("B" & NR)

End Sub

Answer 2

Just quick and dirty fixing:

change the last to: Range("B1:I" & NR).Select

The long version:

The range should be defined by one of those ways: Range("B1:I10") or Range("B:I").

In your code, you are using a middle way Range("B:I10"), and that is not enough.

For your code it is a good idea to write a condition after the NR = Range("A"... like this one:

If (NR < 1) Then NR = 1 Thus, it will give you some value for the row of B and it will not be an error.

Answer 3

NR is getting the first blank row in whatever sheet happens to be active. You want the first blank row in sheet2. You don't need to do anything except specify the start of the destination - a copy will fill the range needed. Also do not select cells or activate sheets, it's pointless, unnecessary and slow.

 Dim NR As Long
 NR = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
 Worksheets("Sheet1").Range("A2:I2").copy _
       destination:= Worksheets("Sheet2").Range("B" & NR)