catzeyes79
New Member
- Joined
- May 24, 2020
- Messages
- 2
- Office Version
- 365
- 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
Thanks in advance
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
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