Good afternoon, I'm trying to create a macro that at a click of the button will
cause my open spreadsheet to pull data from a closed master spreadsheet.

the length of rows changes daily sometimes 120 others only 50 how do i make
it search until (A) colum shows as blank. Data starts in A2 - CF2 (Row 1 is
headings)

thank you for your help. this is what I've tried with no success.

Sub test()
'
' test Macro
' Macro recorded 5/1/2007 by Allstate
'

'
ChDir _
"D:\documents and settings\ppor2\Desktop\Work
Projects\Pitney\Pitney_Spreadsheet\Original"
Workbooks.Open Filename:= _
"D:\documents and settings\ppor2\Desktop\Work
Projects\Pitney\Pitney_Spreadsheet\Original\Prodda ta_APS1.xls"
Range("A1").Select
Do Until IsEmpty(ActiveCell)
Selection.Copy
Windows("Book1").Activate
Rows("1:1").Select
ActiveSheet.Paste
End Sub

Here is an example of a loop until the activecell is emty:


Do Until IsEmpty(ActiveCell)
'Do whatever
ActiveCell.Offset(1, 0).Activate
Loop


Here is how I would do what you are trying to do though:

Sub CopyFromSourceSheet()
Dim OpenSht, SrcSht As Worksheet, _
SrcBk As Workbook, _
SrcRange As Range

Set OpenSht = ActiveSheet
Set SrcBk = Workbooks.Open("D:\documents and settings\ppor2\Desktop
\" & _
"WorkProjects\Pitney
\Pitney_Spreadsheet\" & _
"Original\Proddata_APS1.xls")
Set SrcSht = SrcBk.Sheets(1)
Set SrcRange = SrcSht.Range("A2:CF" & _
SrcSht.Range("A2").End(xlDown).Row)
SrcRange.Copy
OpenSht.Range("A2").PasteSpecial

Set OpenSht = Nothing
Set SrcSht = Nothing
Set SrcBk = Nothing
End Sub


Regards,

David Miller

Dave,

Thank you, I'm only 1 weeks into VBA I really appreciate your help with this.
it worked fine.

"Dave Miller" wrote:

Here is an example of a loop until the activecell is emty:


Do Until IsEmpty(ActiveCell)
'Do whatever
ActiveCell.Offset(1, 0).Activate
Loop


Here is how I would do what you are trying to do though:

Sub CopyFromSourceSheet()
Dim OpenSht, SrcSht As Worksheet, _
SrcBk As Workbook, _
SrcRange As Range

Set OpenSht = ActiveSheet
Set SrcBk = Workbooks.Open("D:\documents and settings\ppor2\Desktop
\" & _
"WorkProjects\Pitney
\Pitney_Spreadsheet\" & _
"Original\Proddata_APS1.xls")
Set SrcSht = SrcBk.Sheets(1)
Set SrcRange = SrcSht.Range("A2:CF" & _
SrcSht.Range("A2").End(xlDown).Row)
SrcRange.Copy
OpenSht.Range("A2").PasteSpecial

Set OpenSht = Nothing
Set SrcSht = Nothing
Set SrcBk = Nothing
End Sub


Regards,

David Miller