Issue

This Content is from Stack Overflow. Question asked by Thomas Mans

I want to copy a selected range in excel to a new workbook but I need to copy with out the formulas, the problem I am having is that I have Buttons on the sheet that run Macros and they are also copied over, I only need the range that is defined with the formating but no formulas.

Here is the code I run.

Sub CopyToAnotherBook()

ActiveSheet.Copy

Cells.Copy

Range(“B1:J58”).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub

Solution

try this:

Sub doCopyRangeValuesToNewWorkbook()

    Dim wb As Workbook, targetWB As Workbook
    Dim ws As Worksheet, targetWS As Worksheet
    
    Set wb = ThisWorkbook 'source workbook
    Set ws = wb.ActiveSheet 'source work sheet
    
    Set targetWB = Workbooks.Add 'target workbook
    Set targetWS = targetWB.Sheets.Add 'target worksheet
    
    targetWS.[B1:J58].Value = ws.[B1:J58].Value

    'or as in your example, you can use Copy method
    'ws.[B1:J58].Copy: targetWS.[B1:J58].PasteSpecial Paste:=xlPasteValues
    
End Sub

This Question was asked in StackOverflow by Thomas Mans and Answered by Vasily It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.