Summary workbook

[harry96794]

I am trying to figure out a methodology to accomplish the following:

I have a series of workbooks (about 80) in a single directory in Excel 2007. I want to take a report that is contained on one of the sheets in each workbook , copy all the rows from A1 thru the top of a pivot table on the sheet, and place the data on a new worksheet for all workbooks in the directory.

Visually

Workbook1 - contains Pivot Report
Workbook2 - contains Pivot Report (possible different size/range of cells than previous)
Workbook3 - contains Pivot Report (also possibly different)...
...etc to Workbook #80 in the same directory.

New WorkbookReport:
contains
range A1..k10 from Workbook 1 (need to figure out location of k10 - only an example)
space
range A1..k50 from Workbook 2 (need to figure out location of k50 - only an example)
space
range A1..K30 from Workbook 3 (need to figure out location of k30 - only an example)
space
etc..down to workbook #80.

Is this possible? I am a novice to VB Programming and have a code snippet to open all the workbooks in the directory, process them, close and move to the next workbook.

My issue is I cant figure out the code to give me the beginning location of each pivot table so I can use that range to copy the cells into the new workbook.

Thanks in advance for any assistance!

[JBeaucaire]

You can use this snippet to determine the last row of data in a particular column

Please Login or Register  to view this content.

Another way:

Please Login or Register  to view this content.

[jaslake]

Do all the worksheets to be copied from each workbook have the same Sheet Name (eg: PivotTable3) or, alternately, are they all the same Sheet Number (eg: Sheet3)?
J

[jaslake]

You could try something like this

Please Login or Register  to view this content.

This will copy all rows from A1 to the Last Row of the source WS.
This statement

My issue is I cant figure out the code to give me the beginning location of each pivot table so I can use that range to copy the cells into the new workbook.

is confusing. I thought you stated that each Pivot Table started in A1 and that you couldn't locate the end. The above line of code will copy the entire table. This line of code

Please Login or Register  to view this content.

will paste to the first available line in the Master Sheet.
Hope this helps. J

[JBeaucaire]

You could try something like this

Please Login or Register  to view this content.

I believe that's a variation of the suggestion already provided.

Please Login or Register  to view this content.

Jaslake, you should get in the habit of removing Excel 2003 specific limitations from your code suggestion. 65536 rows is Excel 2003.

TIP OF THE DAY:
A better standard VBA method for "starting at the bottom of the sheet" would be:

Please Login or Register  to view this content.

This makes your code work in any version of Excel and any length of data.

[jaslake]

Jaslake, you should get in the habit of removing Excel 2003 specific limitations from your code suggestion. 65536 rows is Excel 2003.

TIP OF THE DAY:
A better standard VBA method for "starting at the bottom of the sheet" would be:


Code:
LR = Range("K" & Rows.Count).End(xlUp).RowThis makes your code work in any version of Excel and any length of data.

Your point is well taken. I've used both conventions in the past. I need to remember to adopt your suggestion.

Thanks, J