Log In
Register
Home
May 8, 2018
Offline
Dear Mynda
See attached workbook:
I would appreciate if there is anyone that can help with simple VBA Code that can do the following for me.
- I want to save the Template sheet (first sheet) as a new workbook based on the list of ID Numbers in the second sheet called “Active_IDNumbers”. There are 50 ID Numbers, which means I need 50 workbooks to be saved.
- Each time a new workbook is saved, it must adapt the unique ID Number as the filename as well as the sheet name i.e. overwriting the sheet name “Template” with the ID Number.
- The unique ID Number must also automatically fill in cell D3 in the Template i.e. AA_1, AA_2, etc.
- The visibility of the rest of the sheets i.e. Data and Instruction sheets must be set to:
- “2 – xlSheetVeryHidden”
- And finally, the 50 workbooks to be saved to one repository e.g. "C:\Users\jsoap\Desktop\AA_1.xlsx", etc. I can always change the path in the code.
NB: The original workbook must be kept in its original state i.e. no changes as this one will be refreshed with new data so that I can run the code repeatedly.
I trust and hope there is someone out there that can help.
Regards
AA.
Active Member
August 26, 2021
Offline
For doing this programatically you need to master loops.
I recommend you to loop through the list of ID numbers and puting it as a variable name for the new books
After that loop using workbooks.add and the template create a workbook using the name variable.
Using Range object you can define de D3 value of each workbook
Looping through the sheets, Set the worksheet.Visible parameter to veryhidden except in Data and Instructions
Finally, save each workbook using the variable activeworkbook.path
hope this little nuggets helps you, but you need to study loops to get this work done.
May 8, 2018
Offline
Thanks Luis, any response is always welcomed. I really do appreciate the response.
I will definitely try and research "Loops". I will also have to breakdown your response to start capturing and testing the code but it might just take me a while to get it right.
I know I will learn a lot from trial and error, yet will appreciate it if anybody is still willing to provide me with the code. I can always tweak it here and there.
Regards,
1 Guest(s)
