nidenikolev
New Member
- Joined
- Jun 6, 2018
- Messages
- 20
I have a call to macro that creates a folder by date:
and then I want to create folders in that by Executive manager (not working, needs editing):
and then I have things in theworksheet itself that show what to filter and how to name the file.
and then it saves the filename asthe manager in the list in "mgrlvl4" sheet.
How do I modify the.SaveAs location in the main script so that it finds the Date folder &executive mgr that the supervisor filename falls under?
I want it to create a Date Folder -> Executive folders within that date folder -> and then create separate files by managers that report up to the executive
Code:
[SIZE=2][FONT=Consolas][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sPath [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336]sPath = [/COLOR][COLOR=#7d2727]"M:\Lvl3-5Mgrs\"[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#101094]If[/COLOR][COLOR=#303336] Len(Dir(sPath & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd"[/COLOR][COLOR=#303336]), vbDirectory)) = [/COLOR][COLOR=#7d2727]0[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] MkDir (sPath & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd"[/COLOR][COLOR=#303336]))[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][/FONT][/SIZE]
and then I want to create folders in that by Executive manager (not working, needs editing):
Code:
[SIZE=2][FONT=Consolas][COLOR=#101094]Sub[/COLOR][COLOR=#303336] Executive[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#101094]dim[/COLOR][COLOR=#303336] str1 [/COLOR][COLOR=#101094]as[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]string[/COLOR][COLOR=#303336] [/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#101094]dim[/COLOR][COLOR=#303336] wbk1 [/COLOR][COLOR=#101094]as[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]string[/COLOR][/FONT][/SIZE][FONT=Consolas][SIZE=2][COLOR=#303336] [/COLOR][/SIZE][/FONT][SIZE=2][FONT=Consolas][COLOR=#101094]Set[/COLOR][COLOR=#303336] wbk1 = [/COLOR][COLOR=#7d2727]"M:\Lvl3-5Mgrs\VBA_2018 Mid Year TCR_MACROS.xlsx"[/COLOR][/FONT][/SIZE][FONT=Consolas][SIZE=2][COLOR=#303336] [/COLOR][/SIZE][/FONT][SIZE=2][FONT=Consolas][COLOR=#101094]Const[/COLOR][COLOR=#303336] basepath = [/COLOR][COLOR=#7d2727]"M:\Lvl3-5Mgrs\"[/COLOR][COLOR=#303336] & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd"[/COLOR][COLOR=#303336]) <- gets expression [/COLOR][COLOR=#101094]error[/COLOR][/FONT][/SIZE][FONT=Consolas][SIZE=2][COLOR=#303336] [/COLOR][/SIZE][/FONT][SIZE=2][FONT=Consolas][COLOR=#101094]for[/COLOR][COLOR=#303336] x = [/COLOR][COLOR=#7d2727]2[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]to[/COLOR][COLOR=#303336] lastrow[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336]str1 = wkb1.Sheet([/COLOR][COLOR=#7d2727]1[/COLOR][COLOR=#303336]).Range([/COLOR][COLOR=#7d2727]"AQ"[/COLOR][COLOR=#303336] & x).Text[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336]MkDir basepath & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd"[/COLOR][COLOR=#303336]) & str1[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#101094]next[/COLOR][COLOR=#303336] x
END SUB
[/COLOR][/FONT][/SIZE]
and then I have things in theworksheet itself that show what to filter and how to name the file.
Code:
[SIZE=2][FONT=Consolas][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Managers, Manager[/COLOR][/FONT][/SIZE][FONT=Consolas][SIZE=2][COLOR=#858c93]'Refer to all managers in level4[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] Where = Range([/COLOR][COLOR=#7d2727]"AS2"[/COLOR][COLOR=#303336], Range([/COLOR][COLOR=#7d2727]"AS"[/COLOR][COLOR=#303336] & Rows.Count).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336](xlUp))[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#858c93]'Get the managers[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Worksheets([/COLOR][COLOR=#7d2727]"Lvl4"[/COLOR][COLOR=#303336])[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] Managers = .Range([/COLOR][COLOR=#7d2727]"A2"[/COLOR][COLOR=#303336], .Range([/COLOR][COLOR=#7d2727]"A"[/COLOR][COLOR=#303336] & Rows.Count).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336](xlUp))[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]With[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#858c93]'Loop through[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] Manager [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] Managers[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#858c93]'Find them[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] This = FindAll(Where, Manager)[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] This [/COLOR][COLOR=#101094]Is[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7d2727]Nothing[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#303336] Skip[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#858c93]'Create a new file[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] Wb = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)[/COLOR][/FONT][/SIZE]
and then it saves the filename asthe manager in the list in "mgrlvl4" sheet.
Code:
[SIZE=2][FONT=Consolas][COLOR=#303336].SaveAs Filename:=[/COLOR][COLOR=#7d2727]"M:\Lvl3-5Mgrs\"[/COLOR][COLOR=#303336] & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd"[/COLOR][COLOR=#303336]) _[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#303336] & [/COLOR][COLOR=#7d2727]"\"[/COLOR][COLOR=#303336] & Format([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336], [/COLOR][COLOR=#7d2727]"yyyy_mm_dd_"[/COLOR][COLOR=#303336]) & Manager, FileFormat:=xlOpenXMLWorkbook[/COLOR][/FONT][/SIZE]
How do I modify the.SaveAs location in the main script so that it finds the Date folder &executive mgr that the supervisor filename falls under?
I want it to create a Date Folder -> Executive folders within that date folder -> and then create separate files by managers that report up to the executive