Excel vba for loop save each ws in ws

%3CLINGO-SUB%20id%3D%22lingo-sub-1946756%22%20slang%3D%22en-US%22%3EExcel%20vba%20for%20loop%20save%20each%20ws%20in%20ws%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946756%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20i'm%20new%20to%20vba%20coding%20so%20appreciate%20if%20anyone%20could%20help.%20I%20try%20to%20run%20a%20code%20for%20excel%20sheet.%20What%20im%20trying%20to%20do%20is%20for%20it%20to%20run%20the%20code%20in%20loop%20through%20specified%20sheet%2C%20for%20it%20to%20save%20as%20a%20new%20file%20and%20create%20folder%20if%20there%20isnt%20any.%3CBR%20%2F%3EIm%20able%20to%20do%20it%20to%20one%20sheet%20but%20after%20that%20i%20dont%20know%20how%20loop%20code%20works%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EActiveSheet.Next.Select%3C%2FP%3E%3CP%3EIf%20Range(%22F3%22).Value%20%3D%20%22%22%20Then%3CBR%20%2F%3EWindows(%22Import%20OT.xlsm%22).Activate%3CBR%20%2F%3ESheets(%22Cash%22).Select%3CBR%20%2F%3EDim%20filename101%20As%20String%3CBR%20%2F%3EDim%20path101%20As%20String%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3Epath101%20%3D%20Environ(%22UserProfile%22)%20%26amp%3B%20%22%5CDropbox%5CA271%5C5%20Oppgjor%5C%22%20%26amp%3B%202020%20%26amp%3B%20%22%5C%22%3CBR%20%2F%3Efilename101%20%3D%20Range(%22B1%22).Value%20%26amp%3B%20%22.xlsx%22%3CBR%20%2F%3EActiveWorkbook.SaveAs%20path101%20%26amp%3B%20Range(%22A2%22)%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Range(%22A1%22)%20%26amp%3B%20%22%20%22%20%26amp%3B%20filename101%2C%20xlOpenXMLWorkbook%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3ERange(%22F3%22).Activate%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EWorkbooks.Add%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EDim%20Path1%20As%20String%3CBR%20%2F%3EDim%20fpathname1%20As%20String%3CBR%20%2F%3EPath1%20%3D%20Environ(%22UserProfile%22)%20%26amp%3B%20%22%5CDropbox%5CA271%5C4%20Lonnslipper%5C%22%3CBR%20%2F%3Efpathname1%20%3D%20Path1%20%26amp%3B%20Range(%22F3%22)%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Range(%22F2%22)%20%26amp%3B%20%22%20%22%20%26amp%3B%20Range(%22B3%22)%20%26amp%3B%20%22.xlsx%22%3CBR%20%2F%3E%3CBR%20%2F%3Epath01%20%3D%20Environ(%22UserProfile%22)%20%26amp%3B%20%22%5CDropbox%5CA271%5C4%20Lonnslipper%5C%22%20%26amp%3B%20Range(%22F3%22)%3CBR%20%2F%3EDim%20path001%20As%20String%3CBR%20%2F%3EDim%20Folder%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3EFolder%20%3D%20Dir(path01%2C%20vbDirectory)%3CBR%20%2F%3EIf%20Folder%20%3D%20vbNullString%20Then%3CBR%20%2F%3EVBA.FileSystem.MkDir%20(path01)%3CBR%20%2F%3EActiveWorkbook.SaveAs%20filename%3A%3Dfpathname1%2C%20FileFormat%3A%3D51%3CBR%20%2F%3EActiveWorkbook.Close%3CBR%20%2F%3ESheets(%22Cash%22).Select%3C%2FP%3E%3CP%3EElse%3CBR%20%2F%3EActiveWorkbook.SaveAs%20filename%3A%3Dfpathname1%2C%20FileFormat%3A%3D51%3CBR%20%2F%3EActiveWorkbook.Close%3CBR%20%2F%3ESheets(%22Cash%22).Select%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3Ewhat%20i%20want%20it%20to%20do%20is%3B%20run%20through%20specific%20sheet%20(from%20%26amp%3B%20to)%20save%20as%20a%20new%20file%20in%20a%20folder%2C%20if%20the%20folder%20doesnt%20exist%20then%20create%20a%20new%20and%20save%20the%20file%20in%20there.%20The%20reason%20i%20want%20this%20as%20a%20loop%20is%20because%20i%20have%20few%20tens%20of%20sheets.%20For%20it%20to%20work%20i%20think%20i%20need%20to%20write%20it%20specific%20time%2C%20but%20with%20loop%20i%20learned%20i%20dont%20need%20to%20do%20that.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1946756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

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 

0 Replies