Need to copy sheet from a macro workbook and save as .xlsx

Semaj

In the attached file, I set our VBA sheet CodeNames ( to "wsTasks", "wsAction", "wsDependency") as they were added. In Module1, I changed your code to use .Name property (and removed the quotation marks (") to give this working code:

Sub CopySheetsByCodeName()

    Sheets(Array(wsTasks.Name, wsAction.Name, wsDependency.Name)).Copy

End Sub

Now the user can change the worksheet names e.g from "Teach", "Excel", "Demo" to whatever (or alter their sequence) and the code will still copy those sheets.

I assume you will prevent the user opening the file without macros-enabled and wil prevent them from deleting any of these sheets (if not, you'll find methods in the Tutorials section).

Hope this is what you anted. If so, please mark this Answer as Selected.

Hello semaj,

I'm a bit confused in how you are describing worksheet names. In the VB Editor the worksheets (Microsoft Excel Objects) are listed as: SheetCodeName(worksheet tab name) – example: Sheet1(Data). Without a sample file it's difficult to see what is what. In your first line of code to copy it appears that you are using worksheet code names and in your second line of code to copy it appears that you are using variables that have been assigned to each worksheet (ex: wsTasks).

If these are variables, try removing the quote marks  -  use wsTasks instead of "wsTasks".

If this doesn't help, upload a sample file so we can better see what is going on.

Cheers   :-)