Stop Macro at end of range

[poetdi]

Hello all - I am writing a macro from a vendor file that first creates a new workbook, creates report headings, then goes back to the vendor file and copies individual cells to the report workbook, pasting the cell values under the appropriate heading. Note that I'm not copying entire rows, but picking up only about 5 cells from each file to copy to the report.

I have 15 different vendor files. Some of the vendor files have 5 line items to copy from, others have over 100. And because of formatting differences I will have to write a macro for each vendor - but no biggie.

My question is very basic: how can I write simple code to tell the macro stop when it reaches the end of the rows it is copying from? I can't really use a counter since the number of rows will vary by vendor and each month will be different as well.

I have looked through many of the posts here as well as elsewhere, and some ideas are close but not exactly right.

Thank you for any quick responses - my client asked for this to be done "yesterday" so to speak. Help!

Diane

 

[njimack]

Something like this generic example

Dim Last_Row As Long
Dim i as Long
 
With ActiveSheet
 
Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
 
For i = 2 to Last_Row
.Cells(i,1).Copy Workbooks("Book1.xls").Sheets("Sheet1").Cells(i,1)
Next i
 
 
End With

 

[poetdi]

Thank you, but I'll need a little more explanation <blushes in embarrassment>.
Will I have to use the For Next statement for each line item I copy from? I am not sure how to implement this, I guess, is really what I am asking.
Thank you again!


---------------------------------------
Something like this generic example

Dim Last_Row As Long
Dim i as Long

With ActiveSheet

Last_Row = .Range("A" & Rows.Count).End(xlUp).Row

For i = 2 to Last_Row
.Cells(i,1).Copy Workbooks("Book1.xls").Sheets("Sheet1").Cells(i,1)
Next i

 

[Darkspartan]

Thank you, but I'll need a little more explanation <BLUSHES embarrassment in>.
Will I have to use the For Next statement for each line item I copy from? I am not sure how to implement this, I guess, is really what I am asking.
Thank you again!


---------------------------------------
Something like this generic example

Dim Last_Row As Long
Dim i as Long

With ActiveSheet

Last_Row = .Range("A" & Rows.Count).End(xlUp).Row

For i = 2 to Last_Row
.Cells(i,1).Copy Workbooks("Book1.xls").Sheets("Sheet1").Cells(i,1)
Next i

Could you possibly post an example of one of the sheets?

 

[poetdi]

Thanks all... waiting to hear back from the client on how to proceed.