merge worksheets macro

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
 
A

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
 
P

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
 
D

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.
 
D

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top