VBA to copy/paste values for certain sheets into an existing workbook and change the name of the sheet

[MarcDitto]

I am needing to find a way to copy multiple sheets from a workbook (only certain ones) and paste them (values only), change the name of each sheet based on a cell in each sheet. I need the new sheets to be added to an existing workbook.


I have recorded and modified a macro to do this but it only works on the active sheet and I would like to be able to do all requested sheets at one time.


Here is the macro I have.

Sub trial()
'
' trial Macro
'

'
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
Workbooks.Open Filename:= _
"Z:\Production Control\Marc\Extrusion\Schedule Archives.xlsx"
Sheets("Master").Select
Sheets("Master").Copy After:=Sheets(1)
Windows("IPT Ext Schedule2.xlsm").Activate
Cells.Select
Selection.Copy
Windows("Schedule Archives.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If wh.Range("X1").Value <> "" Then
ActiveSheet.Name = wh.Range("X1").Value
End If
ActiveWorkbook.Save
ActiveWindow.Close
Range("A1").Select
End Sub

Continue reading...