Need VBA/Macro help in Excel (I think)

[TroyT]

So I am reading the VBA books and getting lost. I think what I need begins
with If, then statements in VBA.

Anyway what I need is..
A macro that when run will...

Create a sheet called Data
Open/Select sheet1 of the current document
If C8 contains a number then Drill down (Selection.ShowDetail = True)
Copy and paste entire sheet into the Data Sheet

This will then repeat on Cells F8 I8 L8 O8 R8 one after another, copying and
pasting
into the Data sheet. Either of the mentioned cells could have no data, or
some data.

The pasted Data should all be pasted in the Next A column.

I created the general Macro, and tried to edit it into the If then
statement, but get lost..

Any help?

Thx

 

[Mike H.]

Your explanation makes no sense because you state that each time you find
data you copy and paste the entire sheet into data. Each time you paste the
entire sheet into data you'll only have an exact replica of your original
sheet. I don't think that is what you want to do. What exactly do you want
to do?

 

[TroyT]

The C* corresponds to a Pivot table, and if there is data present, it drills
down, and selects all data. The data is then pasted into the next available
line in the Data sheet. The process is repeated again for each Totaled pivot
table value.

I just recorded the macro and it worked up to the point where there is not
data in the pivot table. At that point it stops and errors out.

This is the macro I recorded, and was trying to edit..

"Range("C8").Select
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range("A132").Select
Windows("Main_Book.xlt").Activate
Sheets("Sheet1").Select
Range("F8").Select
Application.CutCopyMode = False
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A263").Select
Windows("Main_Book.xlt").Activate
Sheets("Sheet1").Select
Range("I8").Select
Application.CutCopyMode = False
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A394").Select
Windows("Main_Book.xlt").Activate
Sheets("Sheet1").Select
Range("L8").Select
Application.CutCopyMode = False
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A525").Select
Windows("Main_Book.xlt").Activate
Sheets("Sheet1").Select
Range("O8").Select
Application.CutCopyMode = False
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A656").Select
Windows("Main_Book.xlt").Activate
Sheets("Sheet1").Select
Range("R8").Select
Application.CutCopyMode = False
Selection.ShowDetail = True
Selection.Copy
Windows("123.csv").Activate
ActiveSheet.Paste

"

 

[Mike H.]

I sort of see what you're trying to do. I think a more efficient way would
be to just go to the data that is the source of the pivottable and "run
through" it with a macro, picking up all the data that matches your criteria
and then go to your destination file and write all that information out in
the order that you wish.

I could create a simplistic example if it would help you?

 

[TroyT]

First off, thanks for the help Mike.. The idea of going to the source is what
I have done by drilling down into the pivot table. The Pivot tables were
populated using an external data source from the template. So the template is
run and it poops it right into the pivot table, skipping the data source. I
might be able to use a different macro program I have more background it, I
just know the VBA way would be much faster. I need it to be much faster as
this will be run ~1400 times.

Thanks!

Troy T.