Rhian has attended:
Excel VBA Intro Intermediate course
Excel VBA Saving Worksheets
How do i create a loop so a template worksheet file makes some changes and then saves by the next name in a list each time, closes and then reopens the template file?
RE: Excel VBA Saving Worksheets
Hi Rhian
Thanks for getting in touch. I wanted to clarify - did you want to change the filename, so that each time it is changed or incremented somehow?
There is a thorough procedure listed here that you can copy and paste:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1008
If that doesn't solve your issue, please let us know.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel VBA Saving Worksheets
Thank you for your reply but what i am trying to do is slightly different.
I have a template workbook which needs to open make a change and then save itself, close and then reopen the template, make some changes save itself, close and reopen etc. I have all the code for the changes but i cant get it to save.
On my excel document on one of the worksheets i have in order a list of the names which the file needs to save itself as. The report therefore needs to save as the first item on the list first and then move down to the second and so on.
Im not sure if i need to post another question or not but im also having trouble creating a loop which finds a different value from a list each time so the first time i want it to find the letter a and save itself and then the letter b and so on.
RE: Excel VBA Saving Worksheets
Hi Rhian
Thanks for clarifying.
First, the macro will have to be located on the Personal Macro Workbook. Make sure this is the case instead of it being attached to a particular file as this will result in an error.
Furthermore to store the list of potential filenames you will need to use an Array. You will not have covered this on the Intro / Intermediate course but there are plenty of online resources on how to implement one. In essence you setup a variable as normal but place a number after the name, so Dim MyFileList(4) as String will create a variable of 4 locations. You could then fill this array with a loop such as:
For i = 1 to 4
MyFileList(i) = activecell.offset(i,0)
next i
assuming you have started from a cell one above your list. When you come to save the workbook, use the array name as your filename.
An approach similar to that described above should also answer your other question regarding storing different values from a list.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
