JOEYSCLEE
08-01-2017, 09:47 AM
Hi, there
I have the macro (Samples Report) workbook for copying 1 worksheet. The data of the workbook is starting from Column A to Column BA.
After running Macro Samples report - Testing, the data of the new worksheet is ended to Column AW instead of Column BA. Just wondering the data of cell BA2 is blank so that the data of Column AY to Column BA cannot be copied to the new worksheet.
Would you please :help to review the code in the macro file and advise how to fix the error?
'find correct area to copy
Dim firstRow As Long
firstRow = tempWs.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row
Dim lastRow As Long
lastRow = tempWs.Cells(tempWs.Rows.Count, "A").End(xlUp).Row
Dim lastCol As Long
lastCol = tempWs.Cells(firstRow + 1, tempWs.Columns.Count).End(xlToLeft).Column 'prevent empty last column
Set end_cell = tempWs.Cells(lastRow, lastCol)
tempWs.Range(start_cell, end_cell).Copy
I have the macro (Samples Report) workbook for copying 1 worksheet. The data of the workbook is starting from Column A to Column BA.
After running Macro Samples report - Testing, the data of the new worksheet is ended to Column AW instead of Column BA. Just wondering the data of cell BA2 is blank so that the data of Column AY to Column BA cannot be copied to the new worksheet.
Would you please :help to review the code in the macro file and advise how to fix the error?
'find correct area to copy
Dim firstRow As Long
firstRow = tempWs.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row
Dim lastRow As Long
lastRow = tempWs.Cells(tempWs.Rows.Count, "A").End(xlUp).Row
Dim lastCol As Long
lastCol = tempWs.Cells(firstRow + 1, tempWs.Columns.Count).End(xlToLeft).Column 'prevent empty last column
Set end_cell = tempWs.Cells(lastRow, lastCol)
tempWs.Range(start_cell, end_cell).Copy