Modify Excel Code to split multiple worksheets to file and save it in seperate location

**Masa1989** · 04-04-2016, 07:57 AM

Hi All

I have this code which splits the multiple excel worksheets into individual excel file and saves it all in same location as active workbook.
Each Excel file is named after the sheet name.
I basically have 5 sheets say MODELA, MODELB, MODELC, MODELD,and MODELE.
What i want is,to save all this 5 excel files in 5 difrnt location which i will specify in code and not as done here in activeworkbook.

Please Login or Register  to view this content.

Any help would considerably reduce the time of my team
Thanks in advance

**Kevin#** · 04-04-2016, 08:32 AM

Try something like this

Please Login or Register  to view this content.

**Masa1989** · 04-04-2016, 09:41 AM

Thanks a lot Kevin..It does exactly what i want.
Wanted to check if it is possible to add today's date along with sheetname?
Say Sheetname is Retail, will it be possible to have 04042016Retail?

Thanks again

**Kevin#** · 04-04-2016, 10:12 AM

To get today's date in VBA use the function Date on its own [Excel equivalent of =Today() ]

Wrap the format function around it to format it as you want.
I always go for YYYYMMDD because the files are then sorted in date sequence based on file name which is my preference. (Use DDMMYYYY if you prefer)

Amendments to the macro:
- declare the variable dateStr as string
- give dateStr a value(=today's date) and format it,
- include strDate as part of the file name

If you want a gap between date and text in the name, use something like:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Masa1989** · 04-05-2016, 12:19 AM

Thank You Kevin..You are genius man!!

Modify Excel Code to split multiple worksheets to file and save it in seperate location

LinkBack

Thread Tools

Rate This Thread

Display

Modify Excel Code to split multiple worksheets to file and save it in seperate location

Re: Modify Excel Code to split multiple worksheets to file and save it in seperate locatio

Re: Modify Excel Code to split multiple worksheets to file and save it in seperate locatio

Re: Modify Excel Code to split multiple worksheets to file and save it in seperate locatio

Re: Modify Excel Code to split multiple worksheets to file and save it in seperate locatio

Thread Information

Users Browsing this Thread

Similar Threads

Save the excel file in to specific location with different name vba code

Macro to Auto save a backup copy in a seperate location OR save file with a pop up

Excel 2013 struggling to save 15mb file with has 120 seperate worksheets with it

[SOLVED] Macro to save Excel file in multiple location

[SOLVED] One Report, create multiple sheets, save each sheet in a seperate excel file

Modify Default Open/Save to Current File Location

VBA code to split sheets and save as seperate CSV files

Bookmarks

Bookmarks

Posting Permissions