Hi, I have a piece of code below which goes through a workbook selecting a range from a pivot table and exporting it to specific sheets. The code is very long and time consuming to change. I have several codes similar to this format too. Is there a way of looping to select each publication in turn and export with one Looping function?
Please Login or Register to view this content.
It's difficult for me to test here, but try something like this:
Please Login or Register to view this content.
A sample workbook showing before/after examples would make this easier to test for you...but try this:
Please Login or Register to view this content.
Last edited by JBeaucaire; 07-07-2010 at 12:02 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JBeaucaire I think your code is more like what I'm looking for, the sheet names are:
"AGN", "AGS", "AGW", "BPI", "CNB", "EGK", "EGS", "EHK", "EHS", "FPN", "FRA", "HUK", "HUS", "LFN", "PHM", "PIL", "PPH", "SPD", "SSH"
Ive attached screenshots (ac names blacked out) the spreadsheet was too large to attatch even when I deleted most of the data.the output.
You can see from output that there is a tab for each publication. before the data is imported only rows 1&2 of each publication sheet exsist.
We used all those sheet names as listed in the array in the code I gave. Take out the strings you don't want.
Sorry, Forgot to mention the errors:
JBeaucaire; I get a runtime error '9'
Subscription out of range.
when I debug it takes me to this line:
Sheets(MyShts(Sht)).Range("A3").PasteSpecial xlPasteValues
And has pasted only 1 account name and no revenues into sheets AGN to EGK
Kafrin; I just get a 'next without for' error for the 'next p' above 'end sub'
I know why Im getting that error JB, there is no EGS, EHS or HUS sheet as the old macro incorporates these into EGK, EHK and HUK respectively, but i can set up extra sheets if it wont work without them
The code works without errors now but only imports the last account name of each pivot refresh into cell A3 of each publication sheet.
My bad...fix this:
Please Login or Register to view this content.
Thats great thanks, now to try and change the rest of my reports
Again, just edit the array to only include the sheets you want to process.
Last edited by JBeaucaire; 07-08-2010 at 06:53 AM.
Hi again, I've changed a similar macro and its working fine, but this one is a little more advanced:
Please Login or Register to view this content.
When I run the above i get a:
Run-time error '1004':
Select method of range class failed
and directed to the following part of the code:
Sheets(MyShts(Sht)).Range("A4").Select
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks