Creating a vba code so the file save in month folders

%3CLINGO-SUB%20id%3D%22lingo-sub-1356509%22%20slang%3D%22en-US%22%3ECreating%20a%20vba%20code%20so%20the%20file%20save%20in%20month%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%3C%2FP%3E%3CP%3EIm%20having%20trouble%20with%20the%20final%20part%20of%20this%20vba%20code%20so%20i%20was%20hoping%20this%20community%20could%20help.%20im%20trying%20to%20create%20a%20vba%20that%20will%20save%20the%20file%20in%20month%20folders.%20Now%20my%20file%20save%20in%20year%20folder%20but%20i%20want%20it%20to%20to%20save%20in%20a%20month%20in%20that%20year%20folder.%20example.%20if%20i%20save%20the%20file%20today%20i%20want%20it%20to%20go%20to%202020%20folder%20and%20find%20the%20mai%20month%20folder%20and%20save%20it%20there.%20if%20i%20do%20it%20next%20month%20then%20the%20same%20thing%20just%20in%26nbsp%3B%26nbsp%3Bjune%20folder.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22F3%22).Value%20%3D%20%22%22%3C%2FP%3E%3CP%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%20%22C%3A%5CUsers%5CUsername%5CDropbox%5CA271%5C5%20Oppgj%C3%B8r%5C2020%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(%22A1%22)%20%26amp%3B%20%22%20%22%20%26amp%3B%20filename101%2C%20xlOpenXMLWorkbook%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20True%3C%2FP%3E%3CP%3Eend%20sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange%20B1%20is%20company%20name%20And%20A1%20is%20month%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20my%20file%20save%20in%202020%20folder%20with%20the%20right%20name%20everything%20fine%20to%20this%20part.%20but%20the%20last%20trouble%20part%20is%20i%20want%20it%20to%20go%20to%20month%20in%20that%20year%20so%20i%20dont%20have%20to%20drag%20the%20files%20always%20to%20its%20month.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1356509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356681%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20vba%20code%20so%20the%20file%20save%20in%20month%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571785%22%20target%3D%22_blank%22%3E%40excelnoob298%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EDim%20filename101%20As%20String%0ADim%20path101%20As%20String%0ADim%20fso%20As%20Object%0A%0AApplication.DisplayAlerts%20%3D%20False%0A%0ASet%20fso%20%3D%20CreateObject(%22Scripting.FileSystemObject%22)%0A%0A%0Apath101%20%3D%20Environ(%22UserProfile%22)%20%26amp%3B%20%22%5CDropbox%5CA271%5C5%20Oppgj%C3%B8r%5C%22%20%26amp%3B%20Year(Date)%20%26amp%3B%20%22%5C%22%0A%0AIf%20Not%20fso.FolderExists(path101)%20Then%0A%20%20%20%20MsgBox%20%22The%20folder%20%22%20%26amp%3B%20path101%20%26amp%3B%20%22%20doesn't%20exist.%22%2C%20vbExclamation%0A%20%20%20%20Exit%20Sub%0AEnd%20If%0A%0Afilename101%20%3D%20Range(%22B1%22).Value%20%26amp%3B%20%22.xlsx%22%0A%0AActiveWorkbook.SaveAs%20path101%20%26amp%3B%20Range(%22A1%22).Value%20%26amp%3B%20%22%5C%22%20%26amp%3B%20filename101%2C%20xlOpenXMLWorkbook%0AApplication.DisplayAlerts%20%3D%20True%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357713%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20vba%20code%20so%20the%20file%20save%20in%20month%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20code%20but%20its%20seems%20like%20the%20code%20dont%20want%20to%20run.%20its%20stops%20at%20Activeworkbook.saveas%20and%20dont%20run%20anymore.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E1.%20I%20do%20have%20folder%20already%20created%20so%20the%20code%20for%20folder%20creating%20i%20dont%20think%20i%20need%20it.%20but%20it%20just%20stops%20working%20when%20the%20code%20comes%20to%20saving%20it%20dont%20do%20it.%26nbsp%3B%3CBR%20%2F%3E2.%20Could%20please%20explain%20if%20that%20environ%20is%20something%20i%20have%20to%20copy%20to%20or%20remove%20it%20and%20why%20is%20the%20username%20in%20brackets%3F%26nbsp%3B%20im%20new%20to%20this%20so%20im%20trying%20to%20learn.%3CBR%20%2F%3E%3CBR%20%2F%3E3.%20Can%20you%20see%20if%20there%20is%20anything%20i%20can%20add%20on%20the%20code%20i%20wrote%20instead%20of%20writing%20all%20new%3F%20If%20its%20not%20possible%20to%20add%20anything%20then%20its%20no%20problem%20i%20can%20just%20go%20with%20that%20code.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358031%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20vba%20code%20so%20the%20file%20save%20in%20month%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571785%22%20target%3D%22_blank%22%3E%40excelnoob298%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Environ%20function%20is%20used%20to%20replace%20the%20bold%20part%20%22%3CSTRONG%3EC%3A%5CUsers%5CUsername%3C%2FSTRONG%3E%5CDropbox%5CA271%5C5%20Oppgj%C3%B8r%5C2020%5C%22%20in%20the%20folder%20location.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20error%20msg%20do%20you%20get%3F%20The%20default%20error%20description%20would%20give%20you%20an%20idea%20about%20why%20the%20code%20gets%20failed.%20If%20the%20error%20msg%20has%20a%20file%20location%20in%20it%2C%20make%20sure%20the%20file%20location%20reported%20in%20the%20error%20msg%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%20the%20fso%20filesystemobject%20checks%20in%20the%20beginning%20if%20the%20path101%20is%20a%20valid%20folder%20location%20and%20exists.%20So%20if%20the%20code%20execution%20passes%20that%20line%20it%20means%20the%20issue%20is%20with%20the%20month%20name%20entered%20in%20the%20A1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInsert%20a%20breakpoint%20at%20the%20line%20which%20saves%20the%20active%20workbook%20and%20just%20before%20this%20line%20place%20the%20line%20given%20below%2C%20so%20that%20the%20code%20will%20stop%20at%20the%20breakpoint%20and%26nbsp%3B%20debug.print%20will%20print%20the%20full%20path%20of%20the%20file%20being%20saved%20in%20the%20Immediate%20Window.%20See%20if%20it%20looks%20good%20in%20there%2C%20if%20not%2C%20tweak%20it%20accordingly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EDebug.Print%20path101%20%26amp%3B%20Range(%22A1%22).Value%20%26amp%3B%20%22%5C%22%20%26amp%3B%20filename101%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358588%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20vba%20code%20so%20the%20file%20save%20in%20month%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BI%20found%20where%20the%20problem%20was%20and%20was%20able%20to%20fix%20it.%20Thanks%20for%20the%20help.%20i%20had%20one%20more%20question%20if%20you%20could%20here%20there%20too.%20the%20code%20i%20run%20now%20is%20the%20code%20i%20sent%20above.%20now%20the%20only%20problem%20is%20that%20how%20i%20learned%20from%20internet%20was%20to%20write%20it%20for%20every%20sheet%20and%20that%20will%20be%20to%20much%20code%20to%20write%20if%20i%20have%20200%20sheets%20to%20work%20on.%26nbsp%3B%3CBR%20%2F%3EDo%20you%20know%20how%20to%20make%20the%20code%20i%20sent%20above%20to%20run%20through%20every%20sheet%20in%20that%20workbook%3F%20and%20also%20the%20same%20with%20this.%20both%20work%20togheter%20now%20but%20the%20problem%20is%20i%20have%20200-300%20sheets%20to%20work%20on%20every%20month%20so%20to%20write%20this%20same%20code%20so%20many%20times%20in%20vba%20with%20different%20path%20names%20makes%20it%20difficult.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20whole%20code%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveSheet.Next.Select%3CBR%20%2F%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%20%22C%3A%5CUsers%5CUsername%5CDropbox%5CA271%5C5%20Oppgj%C3%B8r%5C2020%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%20Path2%20As%20String%3CBR%20%2F%3EDim%20oppgj%C3%B8r1%20As%20String%3CBR%20%2F%3EDim%20fpathname1%20As%20String%3CBR%20%2F%3EPath1%20%3D%20%22C%3A%5CUsers%5CUsername%5CDropbox%5CA271%5C4%20L%C3%B8nnslipper%5C%22%3CBR%20%2F%3EPath2%20%3D%20Range(%22F3%22)%3CBR%20%2F%3Eoppgj%C3%B8r1%20%3D%20Range(%22B3%22)%3CBR%20%2F%3Efpathname1%20%3D%20Path1%20%26amp%3B%20Path2%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Range(%22F2%22)%20%26amp%3B%20%22%20%22%20%26amp%3B%20oppgj%C3%B8r1%20%26amp%3B%20%22.xlsx%22%3CBR%20%2F%3EActiveWorkbook.SaveAs%20filename%3A%3Dfpathname1%2C%20FileFormat%3A%3D51%3CBR%20%2F%3EActiveWorkbook.Close%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20lays%20in%20that%20for%20it%20to%20run%20on%20every%20sheet%20i%20have%20to%20write%20this%20code%20200%20times%20with%20every%20path%20name%20different%20so%20it%20dont%20stop.%20If%20you%20could%20help%20me%20simplify%20it%20if%20possible%20or%20make%20a%20code%20which%20runs%20through%20every%20sheet%20in%20the%20workbook.%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi guys

Im having trouble with the final part of this vba code so i was hoping this community could help. im trying to create a vba that will save the file in month folders. Now my file save in year folder but i want it to to save in a month in that year folder. example. if i save the file today i want it to go to 2020 folder and find the mai month folder and save it there. if i do it next month then the same thing just in  june folder. 

 

Range("F3").Value = ""

Windows("Import OT.xlsm").Activate
Sheets("Cash").Select
Dim filename101 As String
Dim path101 As String
Application.DisplayAlerts = False
path101 = "C:\Users\Username\Dropbox\A271\5 Oppgjør\2020\"
filename101 = Range("B1").Value & ".xlsx"
ActiveWorkbook.SaveAs path101 & Range("A1") & " " & filename101, xlOpenXMLWorkbook
Application.DisplayAlerts = True

end sub

 

Range B1 is company name And A1 is month 

 

Now my file save in 2020 folder with the right name everything fine to this part. but the last trouble part is i want it to go to month in that year so i dont have to drag the files always to its month. 

 

Thanks

4 Replies
Highlighted

@excelnoob298 

 

You may try something like this...

 

Dim filename101 As String
Dim path101 As String
Dim fso As Object

Application.DisplayAlerts = False

Set fso = CreateObject("Scripting.FileSystemObject")


path101 = Environ("UserProfile") & "\Dropbox\A271\5 Oppgjør\" & Year(Date) & "\"

If Not fso.FolderExists(path101) Then
    MsgBox "The folder " & path101 & " doesn't exist.", vbExclamation
    Exit Sub
End If

filename101 = Range("B1").Value & ".xlsx"

ActiveWorkbook.SaveAs path101 & Range("A1").Value & "\" & filename101, xlOpenXMLWorkbook
Application.DisplayAlerts = True
Highlighted

@Subodh_Tiwari_sktneer thanks for the code but its seems like the code dont want to run. its stops at Activeworkbook.saveas and dont run anymore. 

1. I do have folder already created so the code for folder creating i dont think i need it. but it just stops working when the code comes to saving it dont do it. 
2. Could please explain if that environ is something i have to copy to or remove it and why is the username in brackets?  im new to this so im trying to learn.

3. Can you see if there is anything i can add on the code i wrote instead of writing all new? If its not possible to add anything then its no problem i can just go with that code. 

Thanks

Highlighted

@excelnoob298 

 

The Environ function is used to replace the bold part "C:\Users\Username\Dropbox\A271\5 Oppgjør\2020\" in the folder location.

 

What error msg do you get? The default error description would give you an idea about why the code gets failed. If the error msg has a file location in it, make sure the file location reported in the error msg exists.

 

Though the fso filesystemobject checks in the beginning if the path101 is a valid folder location and exists. So if the code execution passes that line it means the issue is with the month name entered in the A1.

 

Insert a breakpoint at the line which saves the active workbook and just before this line place the line given below, so that the code will stop at the breakpoint and  debug.print will print the full path of the file being saved in the Immediate Window. See if it looks good in there, if not, tweak it accordingly.

 

 

Debug.Print path101 & Range("A1").Value & "\" & filename101

 

 

Highlighted

@Subodh_Tiwari_sktneer I found where the problem was and was able to fix it. Thanks for the help. i had one more question if you could here there too. the code i run now is the code i sent above. now the only problem is that how i learned from internet was to write it for every sheet and that will be to much code to write if i have 200 sheets to work on. 
Do you know how to make the code i sent above to run through every sheet in that workbook? and also the same with this. both work togheter now but the problem is i have 200-300 sheets to work on every month so to write this same code so many times in vba with different path names makes it difficult. 

This is the whole code; 

 

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 = "C:\Users\Username\Dropbox\A271\5 Oppgjør\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 Path2 As String
Dim oppgjør1 As String
Dim fpathname1 As String
Path1 = "C:\Users\Username\Dropbox\A271\4 Lønnslipper\"
Path2 = Range("F3")
oppgjør1 = Range("B3")
fpathname1 = Path1 & Path2 & "\" & Range("F2") & " " & oppgjør1 & ".xlsx"
ActiveWorkbook.SaveAs filename:=fpathname1, FileFormat:=51
ActiveWorkbook.Close

 

The problem lays in that for it to run on every sheet i have to write this code 200 times with every path name different so it dont stop. If you could help me simplify it if possible or make a code which runs through every sheet in the workbook.

thanks