Hi
I have a master sheet that has around 30 or so tabs, I want to have a macro that will copy each of these (values and formatting only) and save them as individual workbooks and generate the file name based on cells B2 and C2.
IS this something that can be done?
thanks in advance
I have something similar that you can modify:
Basically, mine copies the sheets named 'Data & Graphs' to a new sheet based on the file name contained in cell 6,1 with a prefix of 'RL' and suffix of 'output'. It also clears these values output, but the bare bones is there. The directory to save is stored in cell 1,22 on the main sheet.
Please Login or Register to view this content.
Last edited by marky9074; 12-17-2012 at 06:49 AM.
thanks, but I can't see how I can get this to cycle through all my sheets (that may have different names each month)
So you want all sheets in the workbook, or just selected sheets? If you want selected sheets then I think you would have to absolutely reference them by their tab name, or at least their sheet number in code..
Try this code - Change the highlighted section - Keep this code in the file which contains the sheets which need to be saved -
Copy the Excel VBA codePlease Login or Register to view this content.
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
arlu1201 that works great, apart from it's not copying the formatting across or the graphs that are in the orignal tabs
edit - I've tried changing it to match another macro I had that alost did the same thing, but it's still only copying the data and not formats or graohs
Last edited by BillDoor; 12-17-2012 at 09:33 AM.
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Hi Norie - this is failing at
.SaveAs strPath &"\" & strFileName
any idea why
Also, I've had to change the Vlaue after C2 to "text" as C2 is a date and it didn't like it
edit - fixed the first part, my sheet 1 had blank values in those cells as it's the front sheet.
next issue is that this is copying the formulae across, when I just want the values
Last edited by BillDoor; 12-17-2012 at 10:12 AM.
How is it failing?
How is the date formatted?
Bill
In the first post you only wanted to copy the worksheets, no mention of formats or values.
Anyway to get only values try this.
Please Login or Register to view this content.
Sorry, fixed that bit, I'd editted the last post (it was due to a blank cell)
the next problem is that it's copying the formulae across, can that be stopped? thanks for your help
Hi, Norie, that worked fine with one tweak to copy all the cells instead (it was just starting at B2, then copying that to A1, sot the format was all out)
Thanks for the help, appreciate it (my first post does say values and formatting though)
Bill
In brackets.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks