CODEWITHSUNDEEP
excelvba
anxoestevez
anxoestevez

Reputation: 188

Export sheet as workbook (values only)

I am trying to export several sheets from an existing workbook to a new one, but despite I have included this instruction:

.ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

The output still includes formulas.

Here is the whole code:

Sub Sheet_SaveAs()

Dim wb As Workbook
Dim Path As String

'Defining Strings

Path = "%MYPATH%"

'XLS Generator

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Set wb = ActiveWorkbook
With wb
 .ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
 .SaveAs Path & "Example" & ".xlsx"
 .Close False
End With
End Sub

What am I missing?

Upvotes: 0

Views: 703

Answers (1)

z32a7ul
z32a7ul

Reputation: 3777

You need to refer to the workbook's sheets by their name, not the active one:

wb.Worksheets("Sheet1").UsedRange.Value = wb.Worksheets("Sheet1").UsedRange.Value

To iterate through all the sheets you copied:

Dim varSheetNames As Variant: varSheetNames = Array("Sheet1", "Sheet2", "Sheet3")
' ...
Sheets(varSheetNames).Copy ' Note: ActiveWorkbook.Worksheets or ThisWorkbook.Worksheets (depending on where your code is) would be better
' ...
Dim varName As Variant: For Each varName In varSheetNames
    With wb.Worksheets(varName).UsedRange
        .Value = .Value
    End With
Next varName

Upvotes: 1

Related Questions