GravyG_123
New Member
- Joined
- Jan 9, 2025
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi and really excited to be joining this forum and posting a question for the first time!
I work with market research data and receive an excel file twice a year with the results from a fairly uniform survey.
Results are supplied in a near identical workbook with one tab per question (70 tabs on average)
I want to combine these to allow me to compare the changes for each tab easily.
Each tab has a different number of rows (representing the responses to that question) but the columns are the same for the whole workbook.
The corresponding tabs in the two workbooks will be structured identically 99% of the time (we do change the questionnaire periodically so the rows in some tabs will occasionally not match but happy to deal with this manually as long as a macro does not crash when it finds this.
So if I have a tab labelled T34 in the new data file it will be the same data in the previous data file and the tab will also be labelled T34
There are only 8 columns so seems sensible to just compare the tables side by side rather than try to merge them in some way.
so... can I use VBA to open one workbook, copy a defined number of columns into a third workbook from tab 1, then open the second workbook and copy the same columns from tab 1 and paste them into a specific column of the third workbook and then repeat this process for every tab in the workbook?
Is that feasible?
At the moment I am only really able to do visual spot checks looking for big changes by placing the files side by side on screen. this would be a game-changer for me.
I've added an anonymised picture of how the data looks in one of the workbook tabs
Thank you all so much for your advice.
Graeme
I work with market research data and receive an excel file twice a year with the results from a fairly uniform survey.
Results are supplied in a near identical workbook with one tab per question (70 tabs on average)
I want to combine these to allow me to compare the changes for each tab easily.
Each tab has a different number of rows (representing the responses to that question) but the columns are the same for the whole workbook.
The corresponding tabs in the two workbooks will be structured identically 99% of the time (we do change the questionnaire periodically so the rows in some tabs will occasionally not match but happy to deal with this manually as long as a macro does not crash when it finds this.
So if I have a tab labelled T34 in the new data file it will be the same data in the previous data file and the tab will also be labelled T34
There are only 8 columns so seems sensible to just compare the tables side by side rather than try to merge them in some way.
so... can I use VBA to open one workbook, copy a defined number of columns into a third workbook from tab 1, then open the second workbook and copy the same columns from tab 1 and paste them into a specific column of the third workbook and then repeat this process for every tab in the workbook?
Is that feasible?
At the moment I am only really able to do visual spot checks looking for big changes by placing the files side by side on screen. this would be a game-changer for me.
I've added an anonymised picture of how the data looks in one of the workbook tabs
Thank you all so much for your advice.
Graeme
