Sheets Object problem

[jwise]

The code inserts a worksheet, names this worksheet, and then imports data to this sheet. The added sheet is there, but I am renaming an existing worksheet instead of the added sheet. The imported data would overlay this sheet if I allowed it to continue. Here is the code snippet:

[vba] Sheets.Add
Sheets(Sheets.Count).Select
shName = BldName(impType)
Sheets(Sheets.Count).Name = shName [/vba]

I assumed that the added sheet would be the "last" sheet (highest number or "Count"). This is NOT the case. How do I name this added sheet? FYI: BldName is a macro that constructs this name, and it is working properly (I displayed the constructed name).

[Bob Phillips]

[vba]

Set mySheet = Workheets.Add
mySheet.Name = BldName(impType)
[/vba]

[jwise]

Thanks.

One additional question: Do I need a 'DIM mysheet As Worksheet'?

[Bob Phillips]

You don't need it if you don't have Option Explicit at the head of the mmodule, but I strongly recommend that you have both.

[jwise]

Thanks again for the input.

The code now works, but I am still wondering about why my original attempt did not work. Specifically, if I use the "Sheets(1)" nomenclature, I thought that construct would ALWAYS point to the FIRST worksheet created. Using this logic, and assuming I just added a worksheet, then "Sheets(Sheets.Count)" would point to the just-inserted worksheet.

I know that this is not the case, and because you can add/delete worksheets many times over, this notion of "first one created" could get very fuzzy. Still there must be some logic as to the way Excel would address these sheets. Do you know what it is?

TIA