Summary & Master copy of multiple sheets in the same workbook

[asalamk]

Hi There,
I am having problem combining all sheets in the same workbook in which new sheets are added up to the same workbook and here is my request to you all gurus out there:

-There are multiple sheets in the same workbook on different tabs and keep increasing on new tabs. The names of sheets are in like sheet1. sheet2, sheet3 and on.
- All the sheets have same number of columns and same header but unlimited rows. Some may have 10 but some of them in thousands.

My request is:
1 - Copy all data from all the sheets to Master sheet in order and when new sheet is added and once we run the macro, that new sheet need to be update in the master sheet. Just to keep you informed that whenever OR as many times we run the macro, the number of rows should be equal to all rows in all sheets.
2 - Consolidate data OR summarize data by account number OR say by SIN number i.e. no duplicate account should appear in the summary but the total number income and tax paid should add up by investor.
3 - And if possible to create button to run the macro on either summary or master. Just one button to update summary as well as master.

I have attached sample workbook please your kind help.

Thanks in advance
asalamk

 

[Nebu]

Not sure what u r looking for hope this will help u....

 

[Smallman]

Hi Guys

When referring to anything in VBA the need to select or activate anything is almost never necessary. Dispensing with this saves time as items don't get selected prior to consolidation which means code runs more quickly.

There are other ways to achive this task but this is the way I like to do it. It is clean and runs swiftly.

Option Explicit
 
Sub Combine1()  'Excel VBA to consolidate all sheets (except summary)
Dim ws As Worksheet
Dim sh As Worksheet
 
Set sh = Sheet2 ' Master Sheet
sh.Range("A2", sh.Range("N" & Rows.Count).End(xlUp)).Clear
 
For Each ws In Sheets
    If ws.Name <> "Summary" And ws.Name <> "Master" Then
        ws.Range("A2", ws.Range("N" & Rows.Count).End(xlUp)).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
    End If
Next ws
End Sub

Hope it helps.

Take care

Smallman

 

[asalamk]

Hi Nebu/,
It does updates the master file but when I re run the macro, master file gets update twice even no new sheet added to the workbook. The number of times we click the button, the master tab adds up again and again from 1st sheet to last sheet and in such case master tab info is much more times than all sheets.

I believe, we need to create another button beside or above update button to clean first the master sheet and click update to gather all the information, isn't it? and this will avoid duplicates
I believe you understand what I am looking for?

Hi Smallman,
I did use your macro too and gives me duplicate information when I rerun the macro. This means, the number of times you run the macro, the result will be that number of times.
Thanks and hope to resolve this.
Asalamk

 

[Smallman]

Hi @asalamk


The line where I clear the master sheet before running the code ensures that what you
Describe can't happen. I ran it. Only produced one result no matter how many times you run it.

Can you please elaborate a bit more by posting your file.

Take care

Smallman

 

[asalamk]

Hi Smallman,
When I click the update button OR run the macro, the master file gets updated, however, when I rerun the macro OR click the update button without erasing master file, the same info repeated right after the last row and this end up with the double transaction. The number of times I click the update button, that number of times master file get updated.
Therefore, if you create reset button beside update, we will always use first to erase the master file to erase all the info only from master and click the update so that all the sheets will be copies to master.
I hope you get me.
Thanks a lot.
asalamk

 

[Smallman]

Hi Asalamk

You will notice every time you press the button on the Master sheet only one consolidation happens. The problem you were seeing was your master Sheet is Sheet6.

This should help.

Take care

Smallman

 

[Nebu]

Hi I have Fixed it , I guess U can use any of the files the macro from smallman too works fine. Anyhow here is the file with my update
Thanks Nebu

 

[asalamk]

Hi Smallman,
This is really au some! Thank you very much.
There is one thing I wanted to request.
In the those all tabs and the new incoming files every month, there are more than 90 columns and 1000+ rows and in this case how do I change the VBA?

Also, when there are more than 90 columns, I would like to move the update button to the top that is on A1 and the headers will be in 2nd row in the master file?
Thanks a lot.
asalamk