This codecreates a new folder with a new name and saves various sheets from the book into the folder(SEE MACRO AT THE BOTTOM).
The issue is, (SECTION "3" SEE FULL MACRO)I need two sheets saved in one book. This macro works, however, once saved in the newly created folder, the formulas inside the two sheets still reference the old book, instead of the newly created one.
The two sheets are intertwined.
example of the formulas out of wack.
Affected code in Macro
Here is the entire code for a reference
Any help would be appreciated.
The issue is, (SECTION "3" SEE FULL MACRO)I need two sheets saved in one book. This macro works, however, once saved in the newly created folder, the formulas inside the two sheets still reference the old book, instead of the newly created one.
The two sheets are intertwined.
example of the formulas out of wack.
Code:
=[/COLOR][COLOR=#333333]SUM('[1 INV EST JT TEMP 2016 INC..xlsm]Stocked Items'!AF39[/COLOR][COLOR=#333333]
Affected code in Macro
Code:
[/COLOR]'3. Save an XLSX file of the two sheets ("Stocked Items") & ("Stocked Prices") with a new name from cells ("K1, A5, A11, A13") LOCATED in sheet("Stocked Items")'This new File would be saved in the new folder created in step 1.
With Sheets("Stocked Items")
fName = .Range("K1") & .Range("A58") & .Range("A11") & .Range("A13")
Call CreateFile(.Name, Fpath & fName)
Set wb = workbooks.Open(Fpath & fName & ".xlsm")
ThisWorkbook.Sheets("Stocked Prices").Copy Before:=wb.Sheets(1)
ThisWorkbook.Sheets("Stocked Prices").Cells.Copy wb.Worksheets("Stocked Prices").Range("A1")
wb.Save
wb.Close False
End With
[COLOR=#333333]
Here is the entire code for a reference
Code:
[/COLOR]Private Sub CreateFile(aSheet As String, PathAndName)Application.DisplayAlerts = False
Set wb = workbooks.Add
ThisWorkbook.Sheets(aSheet).Copy Before:=wb.Sheets(1)
wb.SaveAs PathAndName
wb.Close False
Application.DisplayAlerts = True
End Sub
Sub AnewFolder()
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Do You want to Save A New Client Folder?", vbYesNo, "Run Macro")
If Answer = vbYes Then
Call COMPLETECells
Dim ws1 As Worksheet: Set ws1 = Sheets("6 Estimate Print")
Dim wb As Workbook
Dim Fpath As String, pdfName As String, fName As String
'1. Create a new folder with a new name from cells ("A13, C8, C9") the cells are located in ("6 Estimate Print") NOTE (*This sheet will not be saved at this time.*)
'The new Folder would be subfolder of Drywall
Fpath = "C:\Users\Terrance\Dropbox\MCD&F Terrance\1 Drywall Steel Estimates\Drywall Estimates" & "\" & ws1.Range("A13") & ws1.Range("C8") & ws1.Range("C9")
MkDir Fpath
Fpath = Fpath & "\"
'CALL (MACRO)
Call TerranceLaborTrackerRegister
'2. Save an XLSX file of sheet ("Terrance Labor Tracker") with a new name from cells ("B4, B8, D8, D9") LOCATED in sheet("T LABOR TRACKER")
'This new File would be saved in the new folder created in step 1.
With Sheets("Terrance Labor Tracker")
fName = .Range("B4") & .Range("B8") & .Range("D8") & .Range("D9")
Call CreateFile(.Name, Fpath & fName)
End With
'CALL (MACRO)
Call StockedItemRegister
'3. Save an XLSX file of the two sheets ("Stocked Items") & ("Stocked Prices") with a new name from cells ("K1, A5, A11, A13") LOCATED in sheet("Stocked Items")
'This new File would be saved in the new folder created in step 1.
With Sheets("Stocked Items")
fName = .Range("K1") & .Range("A58") & .Range("A11") & .Range("A13")
Call CreateFile(.Name, Fpath & fName)
Set wb = workbooks.Open(Fpath & fName & ".xlsm")
ThisWorkbook.Sheets("Stocked Prices").Copy Before:=wb.Sheets(1)
ThisWorkbook.Sheets("Stocked Prices").Cells.Copy wb.Worksheets("Stocked Prices").Range("A1")
wb.Save
wb.Close False
End With
'CALL (MACRO)
Call ContractTrackerRegister
'4. Save an XLSX file of sheet ("CONTRACT JOB TRACKER") with a new name from cells ("G3, B5, B12, B10 ") LOCATED in sheet("CONTRACT JOB TRACKER")
'This new File would be saved in the new folder created in step 1.
With Sheets("CONTRACT JOB TRACKER")
fName = .Range("G3") & .Range("B5") & .Range("B12") & .Range("B10")
Call CreateFile(.Name, Fpath & fName)
End With
'CALL (MACRO)
Call DATEEST
'5. Save an XLSX file of sheet ("6 Estimate Print") with a new name from cells ("C6, D6, A13, C8, C9") LOCATED in sheet("6 Estimate Print")
'This new File would be saved in A different folder that already exists.
With ws1
fName = .Range("C6") & .Range("D6") & .Range("C8") & .Range("C9")
Call CreateFile(.Name, Fpath & fName)
End With
'CALL (MACRO)
Call DATEEST
'6. Save a PDF file of sheet ("6 Estimate Print") with a new name from cells ("C6, D6, A13, C8, C9") LOCATED in sheet("6 Estimate Print")
With ws1
pdfName = .Range("C6") & .Range("D6") & .Range("A13") & .Range("C8") & .Range("C9")
.ExportAsFixedFormat Type:=xlTypePDF, fileName:=Fpath & pdfName
End With
' Call ALLESTJOBSTOCKTLAB
End If
End Sub
[COLOR=#333333]
Any help would be appreciated.