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 Im 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" 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

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" 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

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" 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

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

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

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" 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

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

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

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" 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

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" wrote in message ...
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" 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

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" wrote in message ...
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" 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