Merge Worksheets

[Mark Jackson]

Hello,

I have a macro that will merge my worksheets into one worksheet called
"Master". It works fine. Is it possible when I merge my worksheets again to
create another worksheet called "Master1"..."Master2"..."Master3"...and so on
without overwriting or deleting the previous "Master" worksheet? The
workbook is used in Share Mode. Here is the macro that I use:

Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object
variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim Rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
sht.Delete
End If

Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master
worksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows are
the header rows in all worksheets
Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count,
Rng.Columns.Count).Value = Rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub

I appreciate your help. T

Thanks
Mark

 

[Michael Gill]

Hi Mark,

Try this:

Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with
objectvariables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim Rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

Sheets(wrk.Worksheets.Count).Select
If ActiveSheet.Name Like "Master*" Then
MyName = Mid(ActiveSheet.Name, (InStr(1, ActiveSheet.Name, "r")
+ 1))
NewSheet = "Master" & (MyName + 1)
Else
NewSheet = "Master" & (MyName + 1)
End If

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = NewSheet '"Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is
Masterworksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows
arethe header rows in all worksheets
Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count,
Rng.Columns.Count).Value = Rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub

Michael

 

[Mark Jackson]

Michael,

Thanks for the reply. I tried your suggestion and it works great! I
appreciate your help.

Sincerely,
Mark

 

[Mark Jackson]

Michael,

I just realized that when I merge for the second time it is merging the
"Master1" sheet with the 3 worksheets. If I open "Master2" worksheet it has
all the worksheets in my workbook plus "Master1" worksheet. Is it possible
to only merge the first 3 worksheets and not the "Master1" worksheet?

Thanks
Mark

 

[Michael Gill]

Hi Mark,

I think this one should do it then...

Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work
withobjectvariables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim Rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

Sheets(wrk.Worksheets.Count).Select
If ActiveSheet.Name Like "Master*" Then
MyName = Mid(ActiveSheet.Name, (InStr(1, ActiveSheet.Name, "r")
+ 1))
NewSheet = "Master" & (MyName + 1)
Else
NewSheet = "Master" & (MyName + 1)
End If

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = NewSheet '"Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it
isMasterworksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
If sht.Name Like "Master*" Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows
arethe header rows in all worksheets
Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count,
Rng.Columns.Count).Value = Rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub

Michael

 

[Mark Jackson]

Thanks Michael. I works great!

Mark

 

[Mark Jackson]

Michael,

One more question. Is there a way to modify my current macro so it will
automatically word wrap each cell so I don't have to manually do it? When I
merge the worksheets none of the cells are set to word wrap. Thanks again
for your help.

Mark

 

[Michael Gill]

Hi Mark,

Just add this bit in at the end of the macro:

Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Michael