CODEWITHSUNDEEP
vbaexcel
sexyyoda
sexyyoda

Reputation: 1

Coping a worksheet without it being activated

I'd like to copy a worksheet at the end of my workbook without it becoming active.

I use this code to copy my "Template" sheet at the end:

ThisWorkbook.Sheets("Template").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

But then my new sheet "Template(2)" become the active sheet. I'd like to remain on "Template" even after the copy.

Is it possible ?

Upvotes: 0

Views: 120

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Is it possible?

It's not. .Copy activates the new sheet, that's just how it is. However nothing stops you from (re-)activating the original sheet after:

With ThisWorkbook.Worksheets("Template")
    .Copy after:=ThisWorkbook.Workheets(ThisWorkbook.Worksheets.Count)
    .Activate
End With

Notice I'm using the Worksheets collection here. The Sheets collection can contain non-worksheet objects, such as a Chart sheet, which is typically not what you're looking for - the Worksheest collection only contains actual Worksheet object.

Note that finding your template sheet in the ThisWorkbook.Worksheets collection everytime you need to use it is not a necessity, and makes your code more frail than it needs to be.

Each Excel object (VBA object actually) has a (Name) property (on top of the Name property which refers to whatever the worksheet is labelled as) that you can edit in the Properties pane (press F4 in the editor) - that name must be a legal VBA identifier, and what VBA does with it is pretty nifty: it declares a global-scope object variable with that name, so if you name your "Template" sheet TemplateSheet, then you can do this:

With TemplateSheet
    .Copy after:=ThisWorkbook.Workheets(ThisWorkbook.Worksheets.Count)
    .Activate
End With

And so on for every "static" worksheet (i.e. sheets that aren't generated by code). By referring to worksheets by their Name property (the tab label), your code will start failing as soon as a user decides to name the tab something else. By referring to the corresponding global-scope identifier, you can label the worksheet tab whatever you like, the code won't care at all.

Upvotes: 1

Related Questions