• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Save multiple Workbooks based a List, Change Sheet Name & Insert Sheet Name in Cell D3|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Save multiple Workbooks based a List, Change Sheet Name & Insert Sheet Name in Cell D3|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosSave multiple Workbooks based a Lis…
sp_PrintTopic sp_TopicIcon
Save multiple Workbooks based a List, Change Sheet Name & Insert Sheet Name in Cell D3
Avatar
Amien Attwood
Cape Town
Member
Members
Level 0
Forum Posts: 18
Member Since:
May 8, 2018
sp_UserOfflineSmall Offline
1
August 24, 2021 - 7:29 pm
sp_Permalink sp_Print

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.

  1. 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.
  2. 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.
  3. The unique ID Number must also automatically fill in cell D3 in the Template i.e. AA_1, AA_2, etc.
  4. The visibility of the rest of the sheets i.e. Data and Instruction sheets must be set to:
    • “2 – xlSheetVeryHidden”
  5. 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.

Avatar
Luis Pirla

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 26, 2021
sp_UserOfflineSmall Offline
2
August 26, 2021 - 2:33 am
sp_Permalink sp_Print sp_EditHistory

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.  

Avatar
Amien Attwood
Cape Town
Member
Members
Level 0
Forum Posts: 18
Member Since:
May 8, 2018
sp_UserOfflineSmall Offline
3
August 26, 2021 - 7:36 pm
sp_Permalink sp_Print

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,

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Emma Klonowski, Murat Hasanoglu
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31912
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.