Rename sheet tab

[Gene Augustin]

Can't guess correct format for change sheet name.


Sub MultiColToSingle()


Dim ShtCount As Long
Dim shtnew As Workbook, shtold As Workbook

ShtCount = Sheets.Count

'get sheet name
sht = Sheets(1).Name
If sht <> "IIF" Then

' Change name to IIF
Sheets(1).Name = "IIF"
Set shtold = Sheets(1).Name 'Error here
'Set shtold = Sheets("IIF").Name 'Tried this, also Error here

Sheets.Add after:=Sheets("IIF")
Sheets(ShtCount).Name = "QIF"
Set shtnew = Sheets("QIF")

End Sub

 

[Laroche J]

The correct syntax is
Sheets(1).Name = "IIF"
The problem is that the following instruction was changing it again to QIF:
Sheets(ShtCount).Name = "QIF"
because ShtCount was set to 1 before you added the second sheet.

I suggest you use the following syntax:
With Sheets.Add(after:=Sheets("IIF"))
.Name = "QIF"
End With

I don't know what was the purpose of
Set shtnew = Sheets("QIF")
especially that you DIMmed shtnew as a Workbook. Probably you meant
Worksheet.

Finally, for shtold (if indeed you meant Dim shtold as Worksheet):
Set shtold = Sheets("IIF")

The command Set can only be used to attribute to the variable a reference to
an object, not one of its property (like Name).

JL
Mac OS X 10.4.11, Office v.X 10.1.9



Gene Augustin wrote on 2009-03-12 16:56:

 

[Gene Augustin]

Thanks, you solved my problem. I really meant worksheet. With the proper
format, everything worked