merge worksheets macro

[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

 

[AB]

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

 

[AB]

oops - type, you surely don't need the 2 'ifs' in this:
if if not sht.name

 

[Patrick Molloy]

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

 

[dhermus]

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 -

Thank you, this provided what I needed.

 

[dhermus]

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 -

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