Macro Row Modification

[Papi]

The following macro is used to open a file and select from the top to the bottom and basically copy the data over to the current workbook, then close the workbook that it opened. The problem is that the macro uses Column A of the workbook that is opened and I am finding that sometimes there are a few cells empty which can stop all data below that from copying over the necessary data. Is there a way to find the bottom without running into such spaces?

Sub Acquire_Data()
'
' Aquire_Data Macro
'
'
Application.ScreenUpdating = False
Sheets("SAW").Select
Application.Run "'Builder Analysis Review (BAR).xlsm'!Show_ALL"
Workbooks.Open Filename:= _
"X:\SERVICE & WARRANTY\SAW Case Files\0 Service and Warranty Master Database.xls"
x = ActiveWorkbook.Name
Application.Goto Reference:="R1C1"
Application.Run "'0 Service and Warranty Master Database.xls'!Show_ALL"
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Builder Analysis Review (BAR).xlsm").Activate
Application.Goto Reference:="R2C1"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
ActiveWindow.ActivateNext
Workbooks(x).Close False
Application.ScreenUpdating = True
ActiveCell.Select
End Sub

 

[Smitty]

You can use Cells(Rows.Count,"A").End(xlUp) to get the last used row, as the Up method won't hiccup on data gaps.

You could also use Cells.Copy if you want to copy the entire sheet.

HTH,

 

[Papi]

Hello Smitty,

Thanks for your help. I'll look at that when I get home. That may work better doing the entire worksheet. Many thanks.