I have a model that needs a macro to copy data in other worksheets into one sheet. The good thing is each sheet has the same rules for starting cell and number of columns. The issue is that the number or rows will differ in each worksheet.
Here is how I am envisioning the setup. (see attachment as well)
1. The worksheet data needs to be copied into is called “Combined”
2. The sheets that need copying are between what I call “bookends”
a. The bookends are Sheet “B” (for beginning) and Sheet “E” (End)
3. The macro will look in the first sheet after (to the right of) Sheet “B”
4. It will identify the range of data. The range will always start in cell A10 and pull the columns A through K
5. It will determine the range (rows down) by finding the first “blank” cell in column A (and not include the blank row).
a. For example, if the first blank after A10 is A20, the range selected will be A10:K19
6. The macro will copy all the data (and formatting) and place it starting in A10 of the “Combined” tab.
7. The macro will repeat this for the next tab between the “B” & “E” bookends, copy and paste the new data range directly under the last data set in the “Combined” tab
8. The macro will repeat this process until it gets to the “E” tab and then ends.
I’m hoping the steps above are systematic enough. Sometimes I can be vague and my intent unclear. If unclear please respond and I will clarify.
Thank you in advance for your time.
Bookmarks