merge worksheets macro

  • Thread starter Thread starter dhermus
  • Start date Start date
D

dhermus

I have a workbook that contains multiple identical worksheets and one
summary sheet. I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.


'Start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution
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
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value
 
Unless i misunderstood, try this:
'Start loop
For Each sht In wrk.Worksheets

if if not sht.name = "YourSummarySheetNameHere" then 'If not
summary sheet, do the stuff, otherwise ignore.


'If worksheet in loop is the last one, stop execution
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
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize
(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

End if
 
you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

IF sht.Name <> mst.Name THEN

' copy the data
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount ))
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

END IF

NEXT



Note: where does the variable colCount get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column
 
you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

    IF sht.Name <> mst.Name THEN

        ' copy the data
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount ))
        mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

    END IF

NEXT

Note: where does the variable colCount  get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column







- Show quoted text -
Click to expand...

Thank you, this provided what I needed.
 
you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

    IF sht.Name <> mst.Name THEN

        ' copy the data
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount ))
        mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

    END IF

NEXT

Note: where does the variable colCount  get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column







- Show quoted text -
Click to expand...

I found I have multiple hidden worksheets that I must also exclude
from my merge. Can you help with this statement?
 
Back
Top