I refer to a now closed thread from 2011 that was answered very neatly by Trebor76.
http://www.ozgrid.com/forum/showthread.php?t=149590
I have modified the macro slightly – see below – however, when copying array formulas, e.g. {SalesAnnually}, instead of converting the array formula to a value, it copies the formula itself.
Is there a way round this?
Secondly, is it possible to modify the Sheets(Array()) line to pick up worksheets from a list in one of the workbooks, example:
Sheet1 (in cell A1)
Sheet2 (in cell A2)
Sheet5 (in cell A3)
Sheets(Array()) seems unforgiving when referencing cells, e.g. Sheets(Array(A1,A2,A3)).Copy
Any help would be gratefully received – thank you.
Code
Sub CopySheets()
Dim Directory, TargetName As String
Dim wst As Variant
Directory = Cells(4, 3)
TargetName = Cells(6, 3) & " - " & Cells(7, 3) & " for " & Cells(8, 3) & " (" & Format(Now(), "d mmm yyyy hh.mm") & ")"
Path = Directory & " " & TargetName & ".xlsx"
If Dir(Path) = vbNullString Then
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
For Each wst In Worksheets
With Cells
.Copy
.PasteSpecial xlPasteValues
End With
Range("A1").Select
Next wst
ActiveWorkbook.SaveAs FileName:=Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
MsgBox "The " & TargetName & ".xlsx has now been created and saved in " & Directory, vbInformation, "File Save As Editor"
Else
MsgBox "The " & TargetName & ".xlsx workbook already exists in " & Directory, vbExclamation, "File Save As Editor"
Exit Sub
End If
Application.CutCopyMode = False
End Sub
Display More