Merge cells to master sheet

[Steve]

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.

 

[Ron de Bruin]

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

 

[Gord Dibben]

See in-line

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