How do I refer to a worksheet in excel vba when it's name is a number

[oaishm]

I have a worksheet with 1208 is the name on its tab. I can do =1208!a1 on any other cell in the workbook except 1208!a1 and get an value. However, worksheets("1208").range("a1").select refuses to select that cell

I thought maybe I'm messing some other portion of the code up so I tried

worksheets("bookings").range("a1").select. This worked.

For extra credit, this is what I'm really trying to get to work.

Worksheets(sourcesheet).Range("k1", Range("k65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=starter, Unique:=True

where sourcesheet is a string equal to "1208" which I'm sure is text!

Thanks

 

[dave3009]

Hi

Welcome to MrExcel Forums

This seems to work

Sub selnew()
Dim sourcesheet As String
sourcesheet = "1208"
Worksheets(sourcesheet).Range("a1").Select
End Sub

 

[Jonmo1]

I can do =1208!a1 on any other cell in the workbook except 1208!a1 and get an value. However, worksheets("1208").range("a1").select refuses to select that cell

Welcome to the board...

In order to select a cell on a specific sheet, that sheet must be selected first, then select the cell.

worksheets("1208").range("a1").select

Will not work, if any sheet other than 1208 is currently active.

so instead, break it into 2 lines

Worksheets("1208").Select
Range("a1").select


BTW, it's not necessary to select sheets/ranges to work with them.

 

[shyrath]

Or why not use the sheet name? (Sheet1 or 2 or 3 etc)
You can see those in the VBA editor.

Worksheets(Sheet1).Range("k1", Range("k65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=starter, Unique:=True

 

[Peter_SSs]

For extra credit, this is what I'm really trying to get to work.

Worksheets(sourcesheet).Range("k1", Range("k65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=starter, Unique:=True

where sourcesheet is a string equal to "1208" which I'm sure is text!

It sounds like you are running this code when some sheet other than '1208' is the active sheet. Because you have not specifically stated what sheet the red range above applies to, it will work this out from the Active Sheet, Not sheet '1208'.

Assuming you have defined the range 'starter' to be in the appropriate sheet/place, this should work better for you. Note the . before each range reference. This will ensure they both refer to Sheets(sourcesheet).

With Worksheets(sourcesheet)
    .Range("k1", .Range("k65536").End(xlUp)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=starter, Unique:=True
End With

Just one other pointer... Excel now (Excel 2007) has more than 65,536 rows so I think it is a good idea to use this more general code to find the bottom cell. Instead of

Range("k65536").End(xlUp)

use

Range("K" & Rows.Count).End(xlUp)

 

[oaishm]

This is the world's greatest message board

 

[oaishm]

Well, I thought everything was solved but it wasn't.

Here's me little code.

Sub healthcare()
'This procedure writes a unique list of account numbers from the employees into a new sheet. Then it's going to make a booking
Dim starter As Range 'cell of sheet we're going to write all the account numbers to
Dim sourcesheet As String 'sheet that contains the data we're going to make a booking for
sourcesheet = Worksheets("booking").Range("f6").Text
Set starter = Worksheets("booking").Range("d8")
starter.CurrentRegion.Delete
Set starter = Worksheets("booking").Range("d8")
Worksheets(sourcesheet).Select
With Worksheets(sourcesheet)
.Range("k1", .Range("k" & Rows.Count).End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=starter, Unique:=True
End With

End Sub

I hoped afterwards it would make a list of unque items. Instead, it makes a name called extract that is equal to bookings!$d$8

I guess I don't understand the advanced filter function

 

[Peter_SSs]

I hoped afterwards it would make a list of unque items.

It did for me.

Instead, it makes a name called extract that is equal to bookings!$d$8

1. Can you explain in more detail what you mean by this?

2. What is in F6 on the "bookings" sheet?

3. Whatever, the anwer to question 2 is, can you confirm that
- a sheet with that name exists, and
- that there is a list of items in column K of that sheet, and
- there is a heading in cell K1 of that sheet?

 

[oaishm]

There has to be a heading on column k? No excel book would list that requirement. Well, instead I had to write a silly little procedure to pull out unique items

 

[Peter_SSs]

There has to be a heading on column k?

Yes, Filtering works on a 'list' and Help defines a 'List' as

list: A series of worksheet rows that contain related data, such as an invoice database or a set of client names and phone numbers. The first row of the list has labels for the columns.

I'm not sure what your 'silly little procedure' was, but if column K does not have a heading in row 1, the code could put one there temporarily so that the Advanced Filter could still be used. Something like this might work for you.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> healthcare()<br>    <SPAN style="color:#00007F">Dim</SPAN> starter <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> sourcesheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("booking")<br>        sourcesheet = .Range("F6").Text<br>        .Range("D8").CurrentRegion.Delete<br>        <SPAN style="color:#00007F">Set</SPAN> starter = .Range("D8")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets(sourcesheet)<br>        .Rows(1).Insert<br>        .Range("K1").Value = "temp"<br>        .Range("K1", .Range("K" & Rows.Count).End(xlUp)).AdvancedFilter _<br>            Action:=xlFilterCopy, CopyToRange:=starter, Unique:=<SPAN style="color:#00007F">True</SPAN><br>        .Rows(1).Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    starter.Delete Shift:=xlUp<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>