ExcelBanter - merge worksheets macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - merge worksheets macro (https://www.excelbanter.com/excel-programming/431264-merge-worksheets-macro.html)

dhermus

merge worksheets macro

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[_2_]

merge worksheets macro

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

On 17 July, 16:20, dhermus wrote:
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[_2_]

merge worksheets macro

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

On 17 July, 16:39, AB wrote:
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

On 17 July, 16:20, dhermus wrote:

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- Hide quoted text -

- Show quoted text -

Patrick Molloy

merge worksheets macro

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" wrote in message
...
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

dhermus

merge worksheets macro

On Jul 17, 11:45*am, "Patrick Molloy"
wrote:
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" wrote in message

...

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- Hide quoted text -

- Show quoted text -

Thank you, this provided what I needed.

dhermus

merge worksheets macro

All times are GMT +1. The time now is 01:17 AM.