VBA paste sheets from array to new workbook as values

Question

I'm trying to write this little macro to copy several sheets stored in an array and paste them as values to new workbook, maintaining sheets' names and order. I've found some solutions but not exactly matching my situation.

Sub tryit()

Dim wo As Workbook, wn As Workbook
Dim so As Worksheet, sn As Worksheet
Dim MyArray As Variant

MyArray = Array("ar1", "ar2", "ar3")

Set wo = ActiveWorkbook
Set wn = Workbooks.Add

For Each so In wo.Worksheets(MyArray)
        Set sn = wn.Worksheets.Add
        sn.Name = so.Name
        so.Cells.Copy
        sn.[A1].PasteSpecial xlPasteValues
        sn.[A1].PasteSpecial xlPasteFormats
    Next so

wn.SaveAs Filename:=ThisWorkbook.Path & "\" & "Report"
wn.Close savechanges:=True

End Sub

Problems with result:

I'm adding new worksheets from array to sheets 1 2 and 3, how to replace them instead of deleting?
Order of added worksheets is reversed (ark3, ark2, ark1).

I've seen some solutions with use of Lbound and Ubound functions, but I'm not sure how to combine them with loop, and I guess it would make code cleaner?

Thanks in advance.

Doug Glancy Doug Glancy · Accepted Answer · 2014-11-29T21:13:20

I think this solves your issues. I'm not sure about the part at the end that deletes the last sheet. I don't know how you wanted to handle that:

Sub tryit()

Dim wo As Workbook, wn As Workbook
Dim so As Worksheet
Dim MyArray As Variant

MyArray = Array("ar1", "ar2", "ar3")

Set wo = ActiveWorkbook
Set wn = Workbooks.Add
'copy all worksheets at once
wo.Worksheets(MyArray).Copy before:=wn.Worksheets(1)

For Each so In wn.Worksheets
    'same effect as PasteSpecial>Values
    so.UsedRange.Value = so.UsedRange.Value
Next so

Application.DisplayAlerts = False
'delete last sheet, the default sheet in original workbook
'if there was more than one, need to adjust
wn.Worksheets(wn.Worksheets.Count).Delete
Application.DisplayAlerts = True
wn.SaveAs Filename:=ThisWorkbook.Path & "\" & "Report"
wn.Close savechanges:=True
End Sub

VBA paste sheets from array to new workbook as values

1 Answers