Consolidating Workbook Sheets (i.e., Tabs)

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

Hi Bob

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

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



"Bob" <[email protected]> wrote in message news:[email protected]...
>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
>

Ron,
Thanks for the link! Since the "Master" sheet will always exist in my
workbook, I assume I can comment out the lines that generate an error message
if "Master" already exists.
Also, can you tell me how to modify your code to copy data from specific
sheets rather than from all of them?
Thanks again for your help.
Bob


"Ron de Bruin" wrote:

> Hi Bob
>
> Try
> http://www.rondebruin.nl/copy2.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> >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
> >
>
>
>

Ron,
Please ignore my last post. I just saw your Tip 1 on how to copy data from
specific sheets rather than from all of them.
Thanks,
Bob


"Ron de Bruin" wrote:

> Hi Bob
>
> Try
> http://www.rondebruin.nl/copy2.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> >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
> >
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> >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
> >
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> >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
>> >
>>
>>
>>

Ron,
Your revised code did the trick. Thanks a million!
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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

Ron,
Your revised code did the trick. Thanks a million!
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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

You can copy the CurrentRegion

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


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



"Bob" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Ron,
As always, thanks! Since I have column headings in row 1 on all the sheets
(including the existing Master sheet), I need to copy the data starting in
row 2.
I attempted to modify the following line to account for this, but I get an
error message:

sh.Range(sh.Rows(2)).CurrentRegion.Copy DestSh.Cells(Last + 1, "A")

Can you help me fix this line?
Thanks again,
Bob


"Ron de Bruin" wrote:

> You can copy the CurrentRegion
>
> See
> http://www.rondebruin.nl/copy2.htm#CurrentRegion
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >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
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Try this Bob

Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)

With sh.Range("A1").CurrentRegion
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last + 1, "A")
End With

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
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" <[email protected]> wrote in message news:[email protected]...
> Ron,
> As always, thanks! Since I have column headings in row 1 on all the sheets
> (including the existing Master sheet), I need to copy the data starting in
> row 2.
> I attempted to modify the following line to account for this, but I get an
> error message:
>
> sh.Range(sh.Rows(2)).CurrentRegion.Copy DestSh.Cells(Last + 1, "A")
>
> Can you help me fix this line?
> Thanks again,
> Bob
>
>
> "Ron de Bruin" wrote:
>
>> You can copy the CurrentRegion
>>
>> See
>> http://www.rondebruin.nl/copy2.htm#CurrentRegion
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Bob" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> >> >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
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Ron,
Thanks a million!!!
Regards, Bob


"Ron de Bruin" wrote:

> Try this Bob
>
> Sub Test2()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
>
> On Error Resume Next
> If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
> On Error GoTo 0
> Application.ScreenUpdating = False
> Set DestSh = ThisWorkbook.Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = LastRow(DestSh)
>
> With sh.Range("A1").CurrentRegion
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last + 1, "A")
> End With
>
> End If
> Next
> DestSh.Cells(1).Select
> Application.ScreenUpdating = True
> Else
> MsgBox "The sheet Master already exist"
> End If
> 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" <[email protected]> wrote in message news:[email protected]...
> > Ron,
> > As always, thanks! Since I have column headings in row 1 on all the sheets
> > (including the existing Master sheet), I need to copy the data starting in
> > row 2.
> > I attempted to modify the following line to account for this, but I get an
> > error message:
> >
> > sh.Range(sh.Rows(2)).CurrentRegion.Copy DestSh.Cells(Last + 1, "A")
> >
> > Can you help me fix this line?
> > Thanks again,
> > Bob
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> You can copy the CurrentRegion
> >>
> >> See
> >> http://www.rondebruin.nl/copy2.htm#CurrentRegion
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Bob" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >> >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
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ron,
Thanks a million!!!
Regards, Bob


"Ron de Bruin" wrote:

> Try this Bob
>
> Sub Test2()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
>
> On Error Resume Next
> If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
> On Error GoTo 0
> Application.ScreenUpdating = False
> Set DestSh = ThisWorkbook.Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = LastRow(DestSh)
>
> With sh.Range("A1").CurrentRegion
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last + 1, "A")
> End With
>
> End If
> Next
> DestSh.Cells(1).Select
> Application.ScreenUpdating = True
> Else
> MsgBox "The sheet Master already exist"
> End If
> 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" <[email protected]> wrote in message news:[email protected]...
> > Ron,
> > As always, thanks! Since I have column headings in row 1 on all the sheets
> > (including the existing Master sheet), I need to copy the data starting in
> > row 2.
> > I attempted to modify the following line to account for this, but I get an
> > error message:
> >
> > sh.Range(sh.Rows(2)).CurrentRegion.Copy DestSh.Cells(Last + 1, "A")
> >
> > Can you help me fix this line?
> > Thanks again,
> > Bob
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> You can copy the CurrentRegion
> >>
> >> See
> >> http://www.rondebruin.nl/copy2.htm#CurrentRegion
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Bob" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >> >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
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ron,
Thanks a million!!!
Regards, Bob


"Ron de Bruin" wrote:

> Try this Bob
>
> Sub Test2()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
>
> On Error Resume Next
> If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
> On Error GoTo 0
> Application.ScreenUpdating = False
> Set DestSh = ThisWorkbook.Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = LastRow(DestSh)
>
> With sh.Range("A1").CurrentRegion
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last + 1, "A")
> End With
>
> End If
> Next
> DestSh.Cells(1).Select
> Application.ScreenUpdating = True
> Else
> MsgBox "The sheet Master already exist"
> End If
> 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" <[email protected]> wrote in message news:[email protected]...
> > Ron,
> > As always, thanks! Since I have column headings in row 1 on all the sheets
> > (including the existing Master sheet), I need to copy the data starting in
> > row 2.
> > I attempted to modify the following line to account for this, but I get an
> > error message:
> >
> > sh.Range(sh.Rows(2)).CurrentRegion.Copy DestSh.Cells(Last + 1, "A")
> >
> > Can you help me fix this line?
> > Thanks again,
> > Bob
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> You can copy the CurrentRegion
> >>
> >> See
> >> http://www.rondebruin.nl/copy2.htm#CurrentRegion
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Bob" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >> >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
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>