Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA/Macro help in Excel (I think)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA/Macro help in Excel (I think)
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA/Macro help in Excel (I think)
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." wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA/Macro help in Excel (I think)
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA/Macro help in Excel (I think)
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. "Mike H." wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |