Consolidating data for pivot table

[Guest]

I have a large file with numerous tabs all containing different financial
information for approximately 80 projects. I would like to create a P&L for
each of the 80 projects but am not sure the best way to do this.

Here is a brief description of how the file is structured:

Tab 1 contains sales information. Each project has approximately 10 rows
worth of sales information for multiple years. So, project #1 has
information in the first 10 rows. Project #2 starts on row 11 and contains
the exact same information but for that particular project and so on down the
sheet.

Tab 2 contains inventory information. Same structure as above. Project #1
begins at the top of the page and each project follows say 13 rows apart.

I was thinking I could write some type of macro to create a master data tab
that I could use to build a pivot table. I would need to pull certain sales
data from Tab 1 for the first project and then have the macro automatically
go down 12 rows and pull the same information for project #2 and so on for
all 80 projects. Then I would need to do the same exercise for Tab 2 since
it contains fewer rows of data and would only need to go down say 8 rows for
project #2 and so on. Hopefully this makes sense to someone other than me .

Anyone have any suggestions on a macro that I could start with or
alternatives to my initial approach?

Thanks in advance for your help. It is always greatly appreciated!!

 

[Myrna Larson]

Are your sheets set up so that each section follows the "rules" for a list? If
so, if the problem is that you have a header above each section, maybe you can
use AutoFilter on the entire range and set it up so you do not see the headers
and/or blank rows. Once you've done that, you can just copy the visible rows
to your new master sheet.

 

[Guest]

Hi Myrna,

Thanks for your feedback. My spreadsheet does follow the rules and you are
correct, I do have header rows. The only concern I have with autofiler is I
would have to do it each time data in the spreadsheet changed on all the tabs
(there are actually 20 in which I would be pulling data from, I just used 2
as an example).

Do you think a macro might work where I could just say go to tab 1, pull
rows 1-5 move down 5 rows pull data from rows 10-14, etc to the end of the
sheet. Then write something similiar for the other 20 tabs? I was thinking I
could then just re-run the macro any time data changes and have it update my
master list.

Thanks again for the suggestion. I would not have thought of using
autofiler .