Solved: Activate sheet after add/sort sheet executed

[CodeMakr]

I'm having trouble figuring how to activate a newly added sheet in a multi-sheet workbook. The workbook has a sheet for each employee, and I have a command button on the "main" sheet which adds/renames (via inputbox) a new sheet and then sorts all the sheets in the workbook (Last name, First name). How do I activate/reference this newly created sheet for my code to format it.

Currently, if I run the format code right after the add sheet code it formats the "main" sheet not the newly added one? However, if I run them separately, the format code works with the newly added sheet.

Any help is greatly appreciated.

[rory]

If you have named the sheet, then you can just refer to it by the name you gave it. You could also store the sheet object as a variable:
[VBA]Dim wks as Worksheet
Set wks = Worksheets.Add
wks.Name = "whatever"[/VBA]

It would probably be better to have your format code take a worksheet object as an argument and do all the formatting to that sheet specifically rather than just running on whatever happens to be the activesheet.

Regards,
Rory

[CodeMakr]

The sheet name is dynamic, currently (i.e., the code for the commandbutton click adds the new sheet and renames it based on the input box). So, I don't have a reference for wks.Name="whatever". Here is my current code.
[VBA]Sub CommandButton2_Click()
Dim TotalSheets
Dim Name As String

TotalSheets = Worksheets.Count - 1
Name = InputBox("New Sheet Name")

If Name = "" Then
MsgBox "Please enter a sheet name", vbOKOnly, "Data Entry Error"
Exit Sub
End If

Worksheets.Add after:=Worksheets(Worksheets.Count)

ActiveSheet.Name = Name
ActiveSheet.Visible = True
[/VBA]

Can you tell me what you mean by having the code take a worksheet object as an argument. Also, will the "name" variable I use for the inputbox work for the sheet reference in the format code?

[rory]

But you do have a reference for the name - you stored it in the Name variable, so you could just refer to Worksheets(Name). Better to do this though:
[vba]Sub CommandButton2_Click()
Dim TotalSheets
Dim Name As String
Dim wks As Worksheet

TotalSheets = Worksheets.Count - 1
Name = InputBox("New Sheet Name")

If Name = "" Then
MsgBox "Please enter a sheet name", vbOKOnly, "Data Entry Error"
Exit Sub
End If

Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wks.Name = Name
wks.Visible = True
[/vba]
I don't know what your format code is now but if you write it as something like:
[vba]Sub FormatSheet(ws As Worksheet)
With ws
.Range("A1;A5").Font.Bold = True
.Range("B1:B5").Interior.ColorIndex = 4
End With
End Sub
[/vba]
then you can add a line at the end of your commandbutton code like:
[vba]FormatSheet wks[/vba]
and it will always work on the newly created sheet, no matter what sheet is active.

Regards,
Rory

[CodeMakr]

I've got to run out of the office for the day. I'll try your suggestions out first thing tomorrow.

Thanks a ton for your help!!
It is greatly appreciated.

[rory]

No problem - let me know if you have any questions.
Rory

[CodeMakr]

I have quite a bit of format code (formulas, fonts, dates, print setup, etc.). Where do I place the FormatSheet(ws As Worksheet) code? In the commandbutton click, at the worksheet lvl, workbook lvl, or personal.xls? I must have it in the wrong place as the format code isn't grabbing the newly added sheet.

[Norie]

What you should do is create a reference to the new sheet when it's added.
[vba]
Set wsNew = Worksheets.Add (After:=Worksheets(Worksheets.Count)
[/vba]
This can then be used in subsequent code.
[vba]
With wsNew
.Range("A1;A5").Font.Bold = True
.Range("B1:B5").Interior.ColorIndex = 4
End With
[/vba]

[rory]

You need to change the declaration of your format routine to match FormatSheet(ws As Worksheet) and ensure that all references to ranges/cells/sheets are qualified to refer to the ws sheet object.
It should be in a standard module in the workbook in which it is being used.
You then call it as I suggested from the end of your commandbutton_click code.
Regards,
Rory

[lucas]

Would it be possible to have a hidden template sheet(pre-formatted) and just copy it each time and rename it?

[CodeMakr]

Rory - forgive my ignorance in object types. I am getting "expected variable or procedure, not module" error.

My addsheet code (via commandbutton) is on my summary sheet (workbook has a sheet for every dept. employee(30) and a summary sheet). I made the changes above, and put the formatsheet code in a module.

[rory]

Did you name the module formatsheet? Module names should never be the same as procedure names.
If that's not the problem, it would be easiest if you could post the code for the commandbutton and the formatsheet routine.
Regards,
Rory

[CodeMakr]

Ahh....yes I did. Let me change and see if that was the problem. With the amount of code I've got for the format sub, it will probably be easier to use Steve's suggestion though. Is this the syntax to use to copy a worksheet, instead of adding one in the commandbutton code above?
[VBA]wks = Sheets("EmpMaster").Copy After:=Worksheets(Worksheets.Count)
[/VBA]

[CodeMakr]

That was it Rory! It is thinkgs like this (my lack of knowledge) that cause me great anguish Thank you for all the help, as well as Norie and Steve

For future reference, can you please let me know if the syntax for referencing and copying a master sheet (that I noted in previous post) is correct.

I learn something new every day that I come on this site!! You guys are AWESOME.