Forum Discussion

excelnoob298's avatar
excelnoob298
Copper Contributor
Dec 02, 2020

Excel vba for loop save each ws in ws

Hi i'm new to vba coding so appreciate if anyone could help. I try to run a code for excel sheet. What im trying to do is for it to run the code in loop through specified sheet, for it to save as a new file and create folder if there isnt any.
Im able to do it to one sheet but after that i dont know how loop code works;

ActiveSheet.Next.Select

If Range("F3").Value = "" Then
Windows("Import OT.xlsm").Activate
Sheets("Cash").Select
Dim filename101 As String
Dim path101 As String
Application.DisplayAlerts = False
path101 = Environ("UserProfile") & "\Dropbox\A271\5 Oppgjor\" & 2020 & "\"
filename101 = Range("B1").Value & ".xlsx"
ActiveWorkbook.SaveAs path101 & Range("A2") & "\" & Range("A1") & " " & filename101, xlOpenXMLWorkbook
Application.DisplayAlerts = True
Else
Cells.Select
Range("F3").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Dim Path1 As String
Dim fpathname1 As String
Path1 = Environ("UserProfile") & "\Dropbox\A271\4 Lonnslipper\"
fpathname1 = Path1 & Range("F3") & "\" & Range("F2") & " " & Range("B3") & ".xlsx"

path01 = Environ("UserProfile") & "\Dropbox\A271\4 Lonnslipper\" & Range("F3")
Dim path001 As String
Dim Folder As String

Folder = Dir(path01, vbDirectory)
If Folder = vbNullString Then
VBA.FileSystem.MkDir (path01)
ActiveWorkbook.SaveAs filename:=fpathname1, FileFormat:=51
ActiveWorkbook.Close
Sheets("Cash").Select

Else
ActiveWorkbook.SaveAs filename:=fpathname1, FileFormat:=51
ActiveWorkbook.Close
Sheets("Cash").Select


End If
End If
End Sub

what i want it to do is; run through specific sheet (from & to) save as a new file in a folder, if the folder doesnt exist then create a new and save the file in there. The reason i want this as a loop is because i have few tens of sheets. For it to work i think i need to write it specific time, but with loop i learned i dont need to do that. 

Thanks 

No RepliesBe the first to reply

Resources