You can copy the CurrentRegion

See
http://www.rondebruin.nl/copy2.htm#CurrentRegion


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
Ron,
Please forgive me for imposing, but can you tell me how to modify your code
so that it copies all rows on a given worksheet up to the first blank row?
For example, if there is data in rows 2 thru 12, and row 13 is blank (or as
an alternative, cell A13 contains a special entry like the phrase "END OF
DATA"), I would want your macro to copy just rows 2 thru 12 over to the
Master worksheet.
Thanks again for your help.
regards, Bob

"Ron de Bruin" wrote:

Use this example
http://www.rondebruin.nl/copy2.htm#rows

Change it to this

Sub Test5()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
Set DestSh = Sheets("Master")
DestSh.Range("A2:IV" & Rows.Count).ClearContents
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.Range(sh.Rows(3), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

End If
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
Ron,

When I ran your code, I noticed that it did not copy the last row of data
from each sheet to Master. Are you aware of that anomaly? Also, how do I
modify your code so that it:

1) Uses an existing sheet called "Master"
2) Copies the data to Master starting with row 2 (I have column labels in
row 1 that I need to preserve

Thanks again for your help.

Regards, Bob


"Ron de Bruin" wrote:

Hi Bob

Try
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
I have a workbook with 5 sheets (i.e., tabs) that I need to consolidate into
a 6th sheet within the same workbook. Obviously, the column headings are the
same on each sheet. I am trying to write a macro that will copy the data
from each of the 5 sheets (columns A thru BD, and always starting with row 7)
and append the data in the 6th sheet. The problem I'm encountering is that
the number of rows of data on each of the 5 sheets is different and can vary
over time. So the macro needs to first determine how many rows of data exist
on a given sheet and then copy that range to the 6th sheet.

Example: If Sheet1 has data in rows 7 thru 19, and Sheet2 has data in rows 7
thru 23, then the macro would copy rows 7 thru 19 in Sheet1 to rows rows 7
thru 19 in Sheet6. Next, it would copy rows 7 thru 23 in Sheet2 to rows 20
thru 37 in Sheet6, etc.

Being new to VBA, I would greatly appreciate any help in writing the
aforementioned macro. Thanks in advance for any assistance.

Bob