merging sheets to master without moving total lines
 

I am currently merging 2 sheets into a master sheet. Each sheet will have a
variable number of rows. My problem is that when the two sheets merge into
one, they bring over the totals row, because it is the last row on the sheet.
I don't know how to exclude the row because it is never on the same row #,
it varies with the number of records.

Here is the part of the code that I am using to merge the sheets:

For Each sh In ActiveWorkbook.Worksheets

'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Current", "1", "2",
"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16",
"17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29",
"30", "31"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = lastrow(DestSh)
shLast = lastrow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
..Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

Next

merging sheets to master without moving total lines
 

Try

Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast -1))


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"marcia2026" wrote in message ...
I am currently merging 2 sheets into a master sheet. Each sheet will have a
variable number of rows. My problem is that when the two sheets merge into
one, they bring over the totals row, because it is the last row on the sheet.
I don't know how to exclude the row because it is never on the same row #,
it varies with the number of records.

Here is the part of the code that I am using to merge the sheets:

For Each sh In ActiveWorkbook.Worksheets

'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Current", "1", "2",
"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16",
"17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29",
"30", "31"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = lastrow(DestSh)
shLast = lastrow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

Next

merging sheets to master without moving total lines
 

Ron, THANK YOU SOOOOOOOOOOO VERY MUCH for all of your help. There is no way
I could have done it without you.

"Ron de Bruin" wrote:

Try

Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast -1))


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"marcia2026" wrote in message ...
I am currently merging 2 sheets into a master sheet. Each sheet will have a
variable number of rows. My problem is that when the two sheets merge into
one, they bring over the totals row, because it is the last row on the sheet.
I don't know how to exclude the row because it is never on the same row #,
it varies with the number of records.

Here is the part of the code that I am using to merge the sheets:

For Each sh In ActiveWorkbook.Worksheets

'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Current", "1", "2",
"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16",
"17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29",
"30", "31"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = lastrow(DestSh)
shLast = lastrow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

Next

merging sheets to master without moving total lines
 

You are welcome

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"marcia2026" wrote in message ...
Ron, THANK YOU SOOOOOOOOOOO VERY MUCH for all of your help. There is no way
I could have done it without you.

"Ron de Bruin" wrote:

Try

Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast -1))


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"marcia2026" wrote in message ...
I am currently merging 2 sheets into a master sheet. Each sheet will have a
variable number of rows. My problem is that when the two sheets merge into
one, they bring over the totals row, because it is the last row on the sheet.
I don't know how to exclude the row because it is never on the same row #,
it varies with the number of records.

Here is the part of the code that I am using to merge the sheets:

For Each sh In ActiveWorkbook.Worksheets

'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Current", "1", "2",
"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16",
"17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29",
"30", "31"), 0)) Then

'Find the last row with data on the DestSh and sh
Last = lastrow(DestSh)
shLast = lastrow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

Next