Copy formula to new workbook, without old workbook reference?

[welshgasman]

Hi all,
I have a workbook GCD.xslm that holds monthly trips for the community car service I volunteer for.
At the end of each month I copy all that into a Master GCD.xlsm workbook.

All works fine, so I have a history of all trips and can answer any questions my controller might ask.

However I have noticed that it inserts the GCD.xlsm name and sometimes the whole path to that workbook, so if I were to change the path or have to rename the monthly workbook, it would all break.

I know I could just paste the values, I do that already for another sheet that goes to the treasurer, but I need the formula, as I do a some stats on the monthly trips for my own benefit.

So, I am asking, is there a way to copy the formulae, but omit the monthly sheet name. The sheets in each file are named the same, basically Master is just a larger version of the monthly sheet.

TIA

 

[HaHoBe]

Hi welchgasman,

do you copy by hand or by code?

How about changing the links in the target workbook? Please be sure to test on a copy of the data/workbook:

Public Sub MrE_1222430_1614114()
' https://www.mrexcel.com/board/threads/copy-formula-to-new-workbook-without-old-workbook-reference.1222430/
' Created: 20221117
' By:      HaHoBe
' Version: 1
' Updated: no
' Reason:  none
Dim lngLink       As Long
Dim varLinks      As Variant
Dim wbWork        As Workbook

'change to suit
Set wbWork = ThisWorkbook
varLinks = wbWork.LinkSources
For lngLink = 1 To UBound(varLinks)
  wbWork.ChangeLink Name:=CStr(varLinks(lngLink)), Newname:=wbWork.Name
Next lngLink
Set wbWork = Nothing
End Sub

Ciao,
Holger

 

[welshgasman]

Hi Holger,
I copy by code with PasteSpecial

Sub AddMonthlyData()
Dim strMonthGCD As String, strMasterGCD As String, strPath As String, strSheet As String
Dim wkbMaster As Workbook, wkbGCD As Workbook, sht As Worksheet
Dim i As Integer, intLastGCD As Integer, intMasterGCD As Integer, intLastRow As Integer, intCopyRows As Integer
Dim blnGCD As Boolean

strMonthGCD = "GCD.xlsm"
strMasterGCD = "GCD Master.xlsm"
Set wkbMaster = ThisWorkbook
strSheet = "SF66OEK"
Set sht = ThisWorkbook.Sheets(strSheet)
intMasterGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

' Check monthly GCD is open
For i = 1 To Workbooks.Count
    Debug.Print Workbooks(i).Name
    If Workbooks(i).Name = strMonthGCD Then
        Debug.Print strMonthGCD & " is open"
        blnGCD = True
    End If
    If Workbooks(i).Name = strMasterGCD Then
        strPath = Workbooks(i).Path
    End If
Next
'If not, open it
If Not blnGCD Then
    Workbooks.Open strPath & "\" & strMonthGCD
Else
    Workbooks(strMonthGCD).Activate
End If

'Copy trips
Set wkbGCD = ActiveWorkbook
Set sht = Sheets(strSheet)
intLastGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Debug.Print "Last GCD:" & intLastGCD
intCopyRows = intLastGCD - 4 + 1
Debug.Print "CopyRows" & intCopyRows

'Now copy data to master workbook
Range("A4:J" & intLastGCD).Copy
wkbMaster.Sheets(strSheet).Activate
Range("A" & intMasterGCD + 1).PasteSpecial

'Now copy new donations column
Range("O4:O" & intLastGCD).Copy
wkbMaster.Sheets(strSheet).Activate
Range("M" & intMasterGCD + 1).PasteSpecial

' Now copy cells for Busy sheet
Set sht = ActiveSheet
Range("M" & intMasterGCD & ":P" & intMasterGCD).Copy
intLastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("M" & intMasterGCD + 1 & ":P" & intLastRow).PasteSpecial

' Now copy Passengers
strSheet = "Passengers"
wkbGCD.Sheets(strSheet).Activate
Set sht = ActiveSheet
intLastGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("A2:J" & intLastGCD).Copy
wkbMaster.Sheets(strSheet).Activate
Range("A2").PasteSpecial

' Now copy Destinations
strSheet = "Destinations"
wkbGCD.Sheets(strSheet).Activate
Set sht = ActiveSheet
intLastGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("A2:E" & intLastGCD).Copy
wkbMaster.Sheets(strSheet).Activate
Range("A2").PasteSpecial

' Now add rows to PivotData
strSheet = "PivotData"
wkbMaster.Sheets(strSheet).Activate
Set sht = ActiveSheet
intLastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("A" & intLastRow & ":E" & intLastRow).Copy
'Selection.Copy
Range("A" & intLastRow + 1 & ":E" & intLastRow + intCopyRows).Select
ActiveSheet.Paste

'now refresh the pivot table and chart
''strSheet = "PivotData"
''wkbMaster.Sheets(strSheet).PivotTables("PivotTable1").RefreshTable
''strSheet = "PivotChart"
''wkbMaster.Sheets(strSheet).PivotTables("PivotTable1").RefreshTable

' Can be done in one go apparently
ActiveWorkbook.RefreshAll

ActiveWorkbook.Save
MsgBox "Monthly Data Copied"

End Sub

Thank you, I will try that out when I get time. I was just thinking of removing the monthly workbook name, but that should work.

 

[welshgasman]

Holger,

That worked a treat as well.

Thank you again.