You can copy the CurrentRegion

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


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



"Bob" <Bob@discussions.microsoft.com> wrote in message news:0615564A-9FB0-4249-94D7-FDBFD39971E9@microsoft.com...
> 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" <Bob@discussions.microsoft.com> wrote in message news:CAD0B57B-BC65-4FA6-A7AB-767E26CC4307@microsoft.com...
>> > 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" <Bob@discussions.microsoft.com> wrote in message news:6D5225AE-8270-453B-BBFE-CE53D72ECF71@microsoft.com...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>