+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

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

    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.

    Sub NewWBS()
    Dim wbNew As Workbook
    Dim ws As Worksheet
    
         For Each ws In ThisWorkbook.Worksheets
                ws.Copy
                Set wbNew = ActiveWorkbook
                wbNew.SaveAs ThisWorkbook.Path & "\" & ws.Name
                wbNew.Close
         Next ws
    End Sub
    Any help would considerably reduce the time of my team
    Thanks in advance

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

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

    Try something like this

    Sub NewWBS()
    Dim PathA As String, PathB As String, PathC As String, PathD As String, PathE As String
    Dim wbNew As Workbook
    Dim ws As Worksheet
    PathA = "c:\myFolde\mysubfolderforModelA"
    'etc for the others
    
         For Each ws In ThisWorkbook.Worksheets
                ws.Copy
                Set wbNew = ActiveWorkbook
                
                If ws.Name = "ModelA" Then wbNew.SaveAs PathA & "\" & ws.Name
                If ws.Name = "ModelB" Then wbNew.SaveAs PathB & "\" & ws.Name
                If ws.Name = "ModelC" Then wbNew.SaveAs PathC & "\" & ws.Name
                If ws.Name = "ModelD" Then wbNew.SaveAs PathD & "\" & ws.Name
                If ws.Name = "ModelE" Then wbNew.SaveAs PathE & "\" & ws.Name
                
                wbNew.Close
         Next ws
    End Sub
    If a response has helped then please consider rating it by clicking on *Add Reputation below the post
    When your issue has been resolved don't forget to mark the thread SOLVED (click Thread Tools at top of thread)

  3. #3
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

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

    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

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

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

    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:
    dateStr & "_" & ws.Name

    Sub NewWBS()
    Dim PathA As String, PathB As String, PathC As String, PathD As String, PathE As String
    Dim dateStr As String
    Dim wbNew As Workbook
    Dim ws As Worksheet
    PathA = "c:\myFolde\mysubfolderforModelA"
    'etc for the others
    dateStr = Format(Date, "YYYYMMDD")
    
         For Each ws In ThisWorkbook.Worksheets
                ws.Copy
                Set wbNew = ActiveWorkbook
                
                If ws.Name = "ModelA" Then wbNew.SaveAs PathA & "\" & dateStr & ws.Name
                If ws.Name = "ModelB" Then wbNew.SaveAs PathB & "\" & dateStr & ws.Name
                If ws.Name = "ModelC" Then wbNew.SaveAs PathC & "\" & dateStr & ws.Name
                If ws.Name = "ModelD" Then wbNew.SaveAs PathD & "\" & dateStr & ws.Name
                If ws.Name = "ModelE" Then wbNew.SaveAs PathE & "\" & dateStr & ws.Name
                
                wbNew.Close
         Next ws
    End Sub

  5. #5
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

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

    Thank You Kevin..You are genius man!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-04-2016, 03:50 AM
  2. Macro to Auto save a backup copy in a seperate location OR save file with a pop up
    By kdsanderson30 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2014, 12:38 PM
  3. Replies: 4
    Last Post: 05-06-2014, 11:35 AM
  4. [SOLVED] Macro to save Excel file in multiple location
    By sidd913 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-11-2014, 01:45 PM
  5. [SOLVED] One Report, create multiple sheets, save each sheet in a seperate excel file
    By sammymalta in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-04-2013, 07:12 AM
  6. Modify Default Open/Save to Current File Location
    By msmithdynamicsgp in forum Excel General
    Replies: 0
    Last Post: 09-21-2011, 04:55 PM
  7. VBA code to split sheets and save as seperate CSV files
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2008, 01:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1