Edit VBA to insert multiple sheets as values into new workbook

Question

Edit VBA to insert multiple sheets as values into new workbook

The code on this forum is what I used as a starting point. I am trying to modify it to copy multiple sheets and paste them as values, not just one sheet.

I copied several sheets using worksheets(array(1,2,3)).copy

. I think the problem With ActiveSheet.UsedRange

is because it only replaces the first sheet as values and leaves the rest of the sheets as formulas.

What do I need to change so that all sheets are inserted as values?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Worksheets(Array("Sheet 1","Sheet 2","Sheet 3").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs "L:\Performance Data\UK Sales\Sales (Latest).xlsx"
    wbNew.Close True
    Application.DisplayAlerts = True
End Sub

+3

excel-vba excel

tenvfa May 03 '17 at 14:47

source to share

1 answer

BruceWayne · Accepted Answer · 2017-05-03T16:12:21+0000

You need to scroll through the sheets:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next ws

So, with your code, you can do it like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wbOld As Workbook, wbNew As Workbook
Dim ws As Worksheet, delWS As Worksheet
Dim i       As Long, lastRow As Long, lastCol As Long
Dim shts()  As Variant
Dim rng As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wbOld = ActiveWorkbook

shts() = Array("Sheet 1", "Sheet 2", "Sheet 3")
Set wbNew = Workbooks.Add
Set delWS = ActiveSheet
wbOld.Worksheets(Array("Sheet 1", "Sheet 2", "Sheet 3")).Copy wbNew.Worksheets(1)
delWS.Delete

For i = LBound(shts) To UBound(shts)
    With wbNew.Worksheets(shts(i))
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
        rng.Value = rng.Value
    End With
Next i

wbNew.SaveAs "L:\Performance Data\UK Sales\Sales (Latest).xlsx"
wbNew.Close True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Note. I don't know which book you want to insert the values into. As above, this is done in the COPIED book, not the original.

Edit VBA to insert multiple sheets as values ​​into new workbook

More articles:

Edit VBA to insert multiple sheets as values into new workbook