How do I stop formulas from referring to an old book when saved as a new one

6TBM6

New Member
Joined
Mar 10, 2018
Messages
1
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.

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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top