CODEWITHSUNDEEP
vbaexcel
Delfalso
Delfalso

Reputation: 175

VBA Script transform formula to value while copying to another workbook

I'm currently trying to write a VB script for the first time and i'm struggling with this new language. I need to copy multiple worksheet to a a new workbook and replace formulas by the calculated values.

I succeded on copying 2 worksheet in a new workbook but I don't understand how to use my own submethod to transform formula into values.

This is the code i have so far :

Sub myMacro()
    Dim wb As Workbook
    Set wb = Workbooks.Add
        ThisWorkbook.Sheets("SHEET_1").Copy Before:=wb.Sheets(1)
        ThisWorkbook.Sheets("SHEET_2").Copy Before:=wb.Sheets(1)
    wb.SaveAs "newWB.xlsx"
End Sub

All i need now is, during the copy, transform formula with values. Thanks

Upvotes: 2

Views: 96

Answers (1)

Jordan
Jordan

Reputation: 4514

Following on from my comment, try this:

Sub myMacro()
Dim wb As Workbook
Set wb = Workbooks.Add
    ThisWorkbook.Sheets("SHEET_1").Copy Before:=wb.Sheets(1)
    ThisWorkbook.Sheets("SHEET_2").Copy Before:=wb.Sheets(1)
    wb.Sheets("SHEET_1").Range("A1:Z1000").Value = wb.Sheets("SHEET_1").Range("A1:Z1000").Value 
    wb.Sheets("SHEET_2").Range("A1:Z1000").Value = wb.Sheets("SHEET_2").Range("A1:Z1000").Value
wb.SaveAs "newWB.xlsx"
End Sub

The above code changes the new worksheets' formulas to values in the range A1:Z1000 (in the new destination workbook).

Upvotes: 1

Related Questions