Splitting workbook with multiple sheets into different folders

catzeyes79

New Member
Joined
May 24, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Good morning,
I hope all are safe and well. I am a very inexperienced VBA coder and have been trying to learn. In the code below I am attempting to take a Workbook with multiple sheets and save each to an different folder. To achieve this I have tried to pleace the folder path in cell A1 of each sheet and ask the code to use that for the folder PATH.


Looking at the watch window it appears to grab the PATH the first time but I can't get it to change the variable.

I appreciate this code is probably a dogs breakfast but for two days into my coding journey...you know ;) I'm using Youtube (WiseOwl) and Google but if anyone can recommend any good learning material I'd be grateful (y)


VBA Code:
Sub SheetSplitter()
   
    Dim ws As Worksheet
    Dim Path As String
    Dim FileName As String
    Dim wb As Workbook
    Set wb = ActiveWorkbook
   
   
    '"C:\Users\myfolder\Desktop\Monthly checks\"
   
    For Each ws In ActiveWorkbook.Worksheets
   
        If ws.Visible = xlSheetVisible Then
            Path = Range("a1").Value
            'Range("a1").Select
            FileName = ws.Name
            ws.Copy
                      
           
            ActiveWorkbook.SaveAs FileName:=Path & ws.Name, FileFormat:=xlOpenXMLWorkbook
           
                                
            ActiveWorkbook.Saved = True
            ActiveWorkbook.Close True
        End If
       
       
    Next ws

   
End Sub


Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You need to qualify the range with the sheet, otherwise it's looking at the active sheet.
VBA Code:
Path = ws.Range("a1").Value
 
Upvote 0
Hi Fluff,

Thanks for the help. It didn't quite achieve what I wanted but that's probably down to my question but it did help me solve a problem I didn't know I had :) .

My main issue was the PATH wasn't being updated by the new sheet info as I couldn't get it to activate. The new code below has solved the issue and I've also made some adjustments to hide and unhide certain data sheets I don't want filing.

As I said I'm a complete novice (I believe the phrase is noob), so the code is probably very clunky. Could you suggest any improvements?

Thanks


VBA Code:
Sub SheetSplitter()
    
    Dim ws As Worksheet
    Dim CurrentSheet As Worksheet
    Dim Path As String
    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim myFileName As String
    Dim WS_Count As Integer
    Dim wsHidden: wsHidden = Array("File List", "I'm hidden", "Month List")
    Dim i As Variant
    
    For Each i In wsHidden
        Worksheets(i).Visible = xlSheetHidden
        
    Next i
    
          
        Path1 = "C:\myfolder\Desktop\Monthly Resource checks\"
        
        Set CurrentSheet = ActiveSheet
        
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then
                                
                    Path2 = ws.Range("a1").Value
                    Path3 = ws.Range("d1").Value
                    myFileName = ws.Name
                    Path = Path1 & Path2 & Path3 & "\"
                    ws.Copy
                               
                    
                    ActiveWorkbook.SaveAs FileName:=Path & myFileName, FileFormat:=xlOpenXMLWorkbook
                              
                                         
                    ActiveWorkbook.Saved = True
                    ActiveWorkbook.Close True
            End If
                    
        Next ws
     
    CurrentSheet.Activate
    For Each i In wsHidden
        Worksheets(i).Visible = xlSheetVisible
        
    Next i
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top