Code to combine sheets
Hello Experts,
I have been exploring ways to combine the 8 sheets in the attached file.
I have tried Power Query and there are too many steps involved and not sure how I can easily change the data source.
I am now looking for code to combine the sheets to one consolidated sheet and I would be doing this on a recurring basis.
There is extraneous data outside of the table (top and bottom) and not so sure how VBA code can be used to only combine what is inside the tables. To facilitate any coding, I can say that the tables start at row 7 for each separate sheet (meaning rows 1-6 for each sheet are not needed).
Column names are the same for each sheet
thank you
grateful for your help.
EE_combineSheets.xlsx
I have been exploring ways to combine the 8 sheets in the attached file.
I have tried Power Query and there are too many steps involved and not sure how I can easily change the data source.
I am now looking for code to combine the sheets to one consolidated sheet and I would be doing this on a recurring basis.
There is extraneous data outside of the table (top and bottom) and not so sure how VBA code can be used to only combine what is inside the tables. To facilitate any coding, I can say that the tables start at row 7 for each separate sheet (meaning rows 1-6 for each sheet are not needed).
Column names are the same for each sheet
thank you
grateful for your help.
EE_combineSheets.xlsx
Saqib Husain
Are you looking for totals for each sheet or a single total for all sheets?
ASKER
Hi, I am not looking for totals...only the raw data. I will create a separate pivot for the totals.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
I am not in front of the computer
Does the code copy each sheet starting at row 8? If i read correctly, it copies the header for the first sheet in row 7 then for each sheet after that its only copying data.
Thank you once again for the expert assistance
I am not in front of the computer
Does the code copy each sheet starting at row 8? If i read correctly, it copies the header for the first sheet in row 7 then for each sheet after that its only copying data.
Thank you once again for the expert assistance
Header from rows 6 and 7. Rest is same.
ASKER
Perfect! I wish i could do that. Thank you again for the help.
ASKER
Hi Saqib, i am going to ask another question because i now need sheet names. Plan to post in a few minutes.
ASKER
Saqib, i just posted the question. I accidentally posted jn VBA only and just modified the category to include excel. Thank you
The sheet names are already there in column A
ASKER
OK I have tested and I can work with the code the way it stands. I can filter for the sheets I do not need. It returns the correct answer.
One thing I would like to request a modification:
Paste Values. Is this possible? AS of right now, the code is copying the formulas and after copying, the formulas are referencing an incorrect cell.
Thank you so much for the help.
Very nice.
One thing I would like to request a modification:
Paste Values. Is this possible? AS of right now, the code is copying the formulas and after copying, the formulas are referencing an incorrect cell.
Thank you so much for the help.
Very nice.