Excel VBA Query

[PhineasC]

This sub will create a new workbook and copy Sheet1, Sheet2 and Sheet3 to the new workbook. As Sheet1 etc. exist in the new workbook then copied sheets will be named Sheet1(1) etc. If this is an issue we could either delete Sheet1 etc. from the new workbook before the copy or rename the sheets in the new workbook

Sub CreateAndCopy()
''' Define this wokbook
Dim wbkSource As Workbook
Set wbkSource = ActiveWorkbook

''' Create a new workbook
Dim wbkTarget As Workbook
Workbooks.Add
Set wbkTarget = ActiveWorkbook

''' Copy the worksheets accross backwards
wbkSource.Worksheets("Sheet3").Copy
AfterwbkTarget.Sheets(3)
wbkSource.Worksheets("Sheet2").Copy
AfterwbkTarget.Sheets(3)
wbkSource.Worksheets("Sheet1").Copy
AfterwbkTarget.Sheets(3)

''' Save and close the new workbook
wbkTarget.SaveAs "Target"
wbkTarget.Close

End Sub


To rename a sheet the syntax
Sheets("Sheet1").Name = "NewName"

The : = syntax id reproduced as

Happy to private if you need further advice