| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving text around cells without moving boarder lines | Excel Discussion (Misc queries) | |||
| merging sheets to master sheet in a certain order | Excel Programming | |||
| Merging Worksheets together into Master Worksheet | Excel Programming | |||
| Moving data from master sheet to new sheets | Excel Worksheet Functions | |||
| Moving primary data from master sheet to individual sheets w/in wo | Excel Discussion (Misc queries) | |||
Hybrid Mode
