code copies all rows?

[orsm6]

Hi all - I hope this explanation makes sense. The code below was one i found here with a small modification to do what I needed.

I call the code via another macro.

Sub copyRawDataScholle()

Dim last_row As Long

'establishes last row in column A
    last_row = Range("A" & Rows.Count).End(xlUp).Row
   

'copies the raw data and pastes back to scraps file only as values.
    Workbooks("Finished Goods Yield Report - MASTER.xlsx").Worksheets("Scholle").Range("A2:z" & last_row).Copy
    Workbooks("AC22 Scraps.xlsm").Worksheets("YR - Scholle").Range("A2").PasteSpecial Paste:=xlPasteValues
    Workbooks("AC22 Scraps.xlsm").Activate
    Worksheets("Production Cycles").Select
   
' enable screen updating 
    Application.ScreenUpdating = True


End Sub

If I step through the code in macro window using F8 it only copies any columns with data to last cell in column A... which at the moment is A150.

but if I run the macro by calling it from the first Macro it seems to copy the data down to row 181.... the last non-blank cell is Z181.
I have checked and ensured there is no data from A151 down..... not sure why it is copying it all.

hope that made sense.

 

[vcoolio]

Hello Orsm6,

It could be something as simple as having a space character inadvertently placed from A151 down. Select the remainder of Column A from A151 down and right click and select 'ClearContents'.

Cheerio,
vcoolio.

 

[orsm6]

Hello Orsm6,

It could be something as simple as having a space character inadvertently placed from A151 down. Select the remainder of Column A from A151 down and right click and select 'ClearContents'.

Cheerio,
vcoolio.

Thanks for your thoughts. I had already tried this

 

[Michael M]

Your last_row reference will be the currently activesheet.....tryt adding the workbook and / or sheetname to the line

 

[orsm6]

Your last_row reference will be the currently activesheet.....tryt adding the workbook and / or sheetname to the line

Oh that makes sense. Thanks very much, added the workbook and the sheet to that line and it solved it.

 

[Michael M]

Glad to help...