VBA ActiveCell.Formula and Indirect or Concatenate

[drhatmrexcel]

I am writing a spreadsheet with a macro that successfully opens a second spreadsheet and I haven’t been able to figure out/write macro code to enter a formula in cell G7 of the second spreadsheet that combines the contents of cells F7 and B7 of the second spreadsheet along with “.xlsm” which is thef ile name and file extension of the first spreadsheet along with cell location Sheet1'!$D$3within that first mentioned spreadsheet.
I thought that the indirect function would give me thenecessary result I have tried using the indirect function but thus far to no avail. I know that the first referenced file has to be open for the indirect function to be successful and the resultant formula will not create a working link In the second spreadsheet back to the contents of the first spreadsheet.
Example 1:
Range("G7").Select
Selection.NumberFormat = "General"
ActiveCell.Formula ="='[INDIRECT(ADDRESS(7,6))&INDIRECT(ADDRESS(7,2))&.XLSM]Sheet1'!$D$3"

And I have also tried code lines such as this and those have not worked either

Example 2:
Range("G7").Select
Selection.NumberFormat = "General"
ActiveCell.Formula ="='[&$F7&$B7&$G$2&]&!Class Sheet'!D$3"

Example 3:
Range("G7").Select
Selection.NumberFormat = "General"
ActiveCell.Formula =Concatenate("='["&$F7&$B7&$G$2&"!ClassSheet'!"&D$3

Example 4:
Dim Fvalue As String
Dim Bvalue As String
'Get values of cells F7 and B7 of the Boiler Plate ClassSheet.xlsm and store them in memory
Fvalue = Range("F7").Value
Bvalue = Range("B7").Value
Range("G7").Value = "='[" & Fvalue& Bvalue & ".xlsm]Class Sheet'!$D$3"

EXAMPLE 5: This one works somewhat in that when the macro runs these lines it requires me to first choose the file and then the sheet within the file where cell D3 is. The contents of referenced cell D3 show up in cell G7 after I confirm the link. But when I close and reopen the file the link is not valid anymore and I am forced to manually point the link to the proper location once again
Range("G7").Select
Selection.NumberFormat = "General"
ActiveCell.Formula = "='[$F7&$B7&$G$2]!ClassSheet'!D$3"

Thanks for any nudge you can shoot me.

 

[rlv01]

Not sure you need to go through all the trouble with cells F7 & B7 to build the workbook name.

Sub Example()
    Dim CellAddress As String
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet


    'Both workbooks are presumed to be open already
    Set WB1 = ThisWorkbook    ' This is the FIRST workbook
    Set WB2 = Workbooks("drhatmrexcel_Second_Workbook.xlsm") ' This is the SECOND workbook


    Set WS1 = WB1.Worksheets("Class Sheet")
    Set WS2 = WB2.Worksheets("Data Sheet")    'You did not mention the name of your second worksheet, so this is an example


    CellAddress = WS1.Range("D3").Address(External:=True)
    With WS2
        .Range("G7").NumberFormat = "General"
        .Range("G7").Formula = "=" & CellAddress
    End With
End Sub

 

[drhatmrexcel]

Here is the code that works for the first two repetitions of the macro. Thanks to rlv01 for the nudge.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub save001to50()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' save001to100 Macro[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' If this file name is Boiler Plate Class Sheet.xlsm then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' this file automatically saves 50 files sequentially from001) up through 050)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' as follows:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' 001) .xlsm[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' 002) .xlsm[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' 003) .xlsm[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' 004) .xlsm[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' up through 050) xlsm[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim CellAddress1As String, CellAddress2 As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim WB1 AsWorkbook, WB2 As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim WS1 AsWorksheet, WS2 As Worksheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' The next line checks and makes sure that the current sheetis named Boiler Plate Class[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'sheet and then proceeds, but if not named Boiler PlateClass Sheet then the macro stops[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If ActiveWorkbook.Name <> "Boiler Plate ClassSheet.xlsm" Then Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'* * * * * * * * * * * * * * * * * * * * * * * * * * * * *[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  Set LastCell =ActiveCell[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] ' Application.ScreenUpdating= False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Worksheets(1).EnableAutoFilter = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Worksheets(1).EnableOutlining = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Worksheets(1).Protect UserInterfaceOnly:=False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Unprotect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Unprotect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'* * * * * * * * * * * * * * * * * * * * * * * * * * * * *'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  'Unhides entirecolumns A through P[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("A3:P3").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Selection.EntireColumn.Hidden = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'*****************************************************************************[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If ActiveWorkbook.Path <> "" Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ChDrive(ActiveWorkbook.Path)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ChDir(ActiveWorkbook.Path)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'001[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   'Unprotect thesheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Unprotect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   'In the two cellrange of D1 and E1 select those two cells as one merged/combined cell[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("D1:E1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   'In this two cellrange of merged cells D1 and E1 enter a formula that combines[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ' an equals signwith the "Track / Sled -" and then the contents of sheet named"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ' Pulling ordercell D7 which is equivelant to what is the first class's track or sled[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   'location[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "=""Track / Sled -""&'Pulling Order.xlsm'!R7C4"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   'Now move thecursor to cell AN1 of the current Boiler Plate Class sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Range("AN1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'In cell AN1 of the Boiler Plate Class sheetenter a formula that combines[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'an equals signwith the contents of cell N7 of the Pulling Order sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'This is thePayout table number for the class[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "='Pulling Order.xlsm'!R7C14"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Protect DrawingObjects:=True, Contents:=True,Scenarios:=True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Password protect the sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Protect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("B3").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveWorkbook.SaveAs Filename:=Range("c1").Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' OPEN PULLING ORDER FILE[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Workbooks.Open"Pulling Order.xlsm"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Both workbooks are presumed to be open already[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WB1 =ThisWorkbook    ' This is the FIRSTworkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WB2 =Workbooks("Pulling Order.xlsm")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'  Set WB2 =Workbooks("drhatmrexcel_Second_Workbook.xlsm") ' This is the SECONDworkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WS1 =WB1.Worksheets("Class Sheet")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WS2 =WB2.Worksheets("Pulling Order")   'You did not mention the name of your second worksheet, so this is anexample[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Enter formulas in cells G7, H7, I7 amd J7 of Pulling Order[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("G7").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Selection.NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]CellAddress =WS1.Range("D3").Address(External:=True)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    With WS2[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("G7").NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("G7").Formula = "=" & CellAddress[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]CellAddress =WS1.Range("E3").Address(External:=True)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    With WS2[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("H7").NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("H7").Formula = "=" & CellAddress[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      ActiveWorkbook.Save[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      ActiveWorkbook.Close[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'002[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Unprotect thesheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Unprotect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'In cell A1 enter"002) "[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("A1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "002) "[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  'In the two cellrange of D1 and E1 select those two cells as one merged/combined cell[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Range("D1:E1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "=""Track / Sled -""&'Pulling Order.xlsm'!R8C4"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  'In cell G1 enterformula for the Class name for this file from Pulling Order cell B8[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("G1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "='Pulling Order.xlsm'!R8C2"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  'In cell K1 enterformula for the Class name for this file from Pulling Order cell B8[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("K1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "='Pulling Order.xlsm'!R8C2"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'In cell AN1 ofthe sheet enter a formula that combines[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'an equals signwith the contents of cell N8 of the Pulling Order sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'This is thePayout table number for the class[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("AN1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "='Pulling Order.xlsm'!R8C14"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  'In cell AP1of thesheet enter a formula that combines[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'an equals signwith the contents of cell HK7 of the Pulling Order sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'This is thePoints table number for the class[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("AP1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "='Pulling Order.xlsm'!R8C219"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Protect DrawingObjects:=True, Contents:=True,Scenarios:=True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Password protectthe sheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Protect Password:="lock"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("B3").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveWorkbook.SaveAs Filename:=Range("c1").Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' OPEN PULLING ORDER FILE[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Workbooks.Open"Pulling Order.xlsm"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Both workbooks are presumed to be open already[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WB1 =ThisWorkbook    ' This is the FIRSTworkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WB2 =Workbooks("Pulling Order.xlsm")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'  Set WB2 =Workbooks("drhatmrexcel_Second_Workbook.xlsm") ' This is the SECONDworkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WS1 =WB1.Worksheets("Class Sheet")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set WS2 =WB2.Worksheets("Pulling Order")   'You did not mention the name of your second worksheet, so this is anexample[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Enter formulas in cells G8, H8, I8 amd J8 of Pulling Order[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("G8").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Selection.NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]CellAddress =WS1.Range("D3").Address(External:=True)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    With WS2[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("G8").NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("G8").Formula = "=" & CellAddress[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]CellAddress =WS1.Range("E3").Address(External:=True)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    With WS2[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("H8").NumberFormat = "General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("H8").Formula = "=" & CellAddress[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      ActiveWorkbook.Save[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      ActiveWorkbook.Close[/COLOR][/SIZE][/FONT]