Last Row is selecting an EXTRA 2000 rows.

[mikenelena]

So I'm looking to copy a range from a newly created, temporary query sheet to a permanent sheet with the code below. Hitting Control + End on the Query2 sheet brings me to I109, exactly what I would expect. Hovering over the LR variable in the code shows 109. Debug.printing the variable shows 109. Yet, for reasons I can't understand, when I try to copy the range, the program is selecting all the way down to row 2109. Can anyone offer any ideas as to what I'm doing wrong?

Thanks!

...Mike

Sub Copy_PasteDataToMainTab() 'This copies the current payroll data from the filtered query into the "Main" payroll tab.


Dim LR As Long
Dim ws2 As Worksheet
Dim ws As Worksheet


Set ws2 = Worksheets("Query2")
Set ws = Worksheets("Main")


ws.Activate


LR = ws2.Cells(Rows.Count, 1).End(xlUp).Row


Application.ScreenUpdating = False


ws2.Activate
      
      Range("A2:G2" & LR).Copy
      ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats  '----> Copies values and number formats only to Main starting in Column A
   
ws.Activate


Application.ScreenUpdating = True
Application.CutCopyMode = False
Range("D1").Select


Call RowColor


End Sub

 

[Fluff]

Re: Last Row is selecting an EXTRA 2000 rows... Please help.

On this line

Range("A2:G2" & LR).Copy

you are concatenating G2 with the value of LR, ie G2109
Simply remove the 2 after the G

 

[mikenelena]

Re: Last Row is selecting an EXTRA 2000 rows... Please help.

You're awesome Fluff! Worked perfectly. Thanks for such a quick reply. That had been torturing me for a couple of hours...

...Mike

 

[Fluff]

Re: Last Row is selecting an EXTRA 2000 rows... Please help.

Glad to help & thanks for the feedback