Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge cells to master sheet
Ron de Bruins code to merge cells is very helpful. I'm trying to modify his
code to "Merge cells from all or some worksheets into one Master sheet". How do you make the range you're copying FROM dynamic? Let's say I want to start in C3 and want to include to column J but the last row may not be the same in each sheet? So I want the range C3..J?? How do I do that? Second question...what if I'm not sure what the last column is? Is there a function that returns the last cell in usedrange? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge cells to master sheet
Try this one
You can also test the last column if you want with the lastcol function If you need help post back. I will help you tomorrow (bed time now for me) Sub Test() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "MergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("MergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "MergeSheet" Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "MergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) 'This example copies everything, if you only want to copy 'values/formats look at the example below the first example sh.Range(sh.Cells(3, "C"), sh.Cells(shLast, "J")).Copy DestSh.Cells(Last + 1, "A") End If Next Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With 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/tips.htm "Steve" wrote in message ... Ron de Bruins code to merge cells is very helpful. I'm trying to modify his code to "Merge cells from all or some worksheets into one Master sheet". How do you make the range you're copying FROM dynamic? Let's say I want to start in C3 and want to include to column J but the last row may not be the same in each sheet? So I want the range C3..J?? How do I do that? Second question...what if I'm not sure what the last column is? Is there a function that returns the last cell in usedrange? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge cells to master sheet
See in-line
On Wed, 12 Dec 2007 14:16:01 -0800, Steve wrote: Ron de Bruins code to merge cells is very helpful. I'm trying to modify his code to "Merge cells from all or some worksheets into one Master sheet". How do you make the range you're copying FROM dynamic? Let's say I want to start in C3 and want to include to column J but the last row may not be the same in each sheet? So I want the range C3..J?? How do I do that? Set rng = Range(Range("C3"), Cells(Rows.Count, "J").End(xlUp)) Second question...what if I'm not sure what the last column is? Is there a function that returns the last cell in usedrange? If data is contiguous you can use currentregion Set rng = Range("C3").CurrentRegion To return the address of last cell in currentregion Set rng = Range("C3").CurrentRegion MsgBox rng(rng.Count).Address Thanks. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge 3 workbooks (and all coniciding worksheets) into 1 master wb | Excel Discussion (Misc queries) | |||
How do I import and merge Excel data into a master project file? | Excel Discussion (Misc queries) | |||
Linked cells from master data sheet and sorting | Excel Worksheet Functions | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions |