Combine 2 Macros

[hibernian3]

Hello,

I have a workbook with 3 sheets, I am trying to copy the first 2 sheets to a new workbook without formulas. Each sheet needs a set range, which is different, can I combine these 2 Macros together?

Sub JointingTracker()
Sheets("jointing Tracker").Copy
Range("A1:P70").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

Sub CableTracker()
Sheets("Cable Tracker").Copy
Range("A1:P46").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Domenic]

Do you mean that you'd like to copy both sheets to the same new workbook, and then convert the formulas to values? If so, try...

Sub CopyValuesToNewWorkbook()

    Sheets(Array("jointing Tracker", "Cable Tracker")).Copy
    
    With Sheets("jointing Tracker").Range("A1:P70")
        .Value = .Value
    End With
    
    With Sheets("Cable Tracker").Range("A1:P46")
        .Value = .Value
    End With
    
End Sub

Hope this helps!

 

[hibernian3]

Thank you so much!! so simple once you see it..

If I wanted the new sheet be name after a cell on sheet 1 +a text input, example... New workbook name = Sheets("Cable Tracker")=G2&"inputtedtext".

Is this easy to add?

 

[Domenic]

The following macro has been amended to name the new workbook, as per your requirement, and to save the workbook in the same folder as the source workbook. Note that if a workbook with the same name already exists, it will be overwritten.

Sub CopyValuesToNewWorkbook()

    Dim strSheetName As String
    
    strSheetName = Sheets("Cable Tracker").Range("G2").Value & "inputtedtext"


    Sheets(Array("jointing Tracker", "Cable Tracker")).Copy
    
    With Sheets("jointing Tracker").Range("A1:P70")
        .Value = .Value
    End With
    
    With Sheets("Cable Tracker").Range("A1:P46")
        .Value = .Value
    End With
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=strSheetName
    Application.DisplayAlerts = True
    
End Sub