Reference a worksheet name that can change 2

[Pudsters]

I need to reference few ranges on a worksheet named Invoice (Invoice), but sometimes its name changes to Invoice 1

How would I allow for either scenario?

Worksheets("Invoice").Range("A1") or Worksheets("Invoice 1").Range("A1")

I tried something like this thinking Invoice is the codename whether it is Invoice or Invoice 1, but it didn't work:

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Invoice")

ws(Invoice).Range("A1")​

 

[SkipVought]

We need to see the actual code that you’re using—-COPY N PASTE

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Pudsters]

HERE IT IS:

 'Populate online timecard
Sub sendtimecard()
On Error GoTo Err_sendtimecard

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next
       
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        
       
        .Navigate "[URL unfurl="true"]http://website.com"[/URL] 'THIS IS LOCAL TEST SITE ONLY!
               
        Do
            DoEvents
        Loop Until .ReadyState = 4 
    
        'Start populating job fields from job1 to job9, based on next empty Job chkbox...
        
        With .Document
            For i = 1 To 9
                If Not .getElementById("chkboxJOB" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById("txtNameJOB" & i).Value = wsINV.Range(RANGE_CUSTNAME)
                    .getElementById("invoiceJOB" & i).Value = wsINV.Range(RANGE_INVOICENUMBER)

                    
'                   .getElementById("txtNameJOB" & i).Value = Worksheets("Invoice").Range(RANGE_CUSTNAME)
'                   .getElementById("invoiceJOB" & i).Value = Worksheets("Invoice").Range(RANGE_INVOICENUMBER)

                                     
                    Exit Sub
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With

    
Exit_sendtimecard:
    Exit Sub
    
Err_sendtimecard:
    MsgBox Err.Description
    Resume Exit_sendtimecard
    
Set ws = Nothing
    
End Sub

 

[SkipVought]

Took your code and I also made named ranges like yours and this works, which means I get the VALUES entered in those two cells...

Sub sendtimecardTEST()
On Error GoTo Err_sendtimecard

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next
    
[b]Debug.Print wsINV.Range("RANGE_CUSTNAME")
Debug.Print wsINV.Range("RANGE_INVOICENUMBER")[/b]
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip already gave you this fix / hint in his post on 21 Aug 18 at 18:42

Put QUOTES around RANGE_CUSTNAME and RANGE_INVOICENUMBER

---- Andy

There is a great need for a sarcasm font.

 

[Pudsters]

That didn't work, I tried it, let me try it again

 

[Pudsters]

Guys, it just won't work with my workbook. I added the quotes. As soon as it tries to enter the customer name, it goes to the error handler with the message: "Object Variable or With Block Variable not set"

 

[SkipVought]

Please COPY N PASTE your current code here.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Pudsters]

I also tried separating the Dims:

Dim ws As Worksheet, wsINV As Worksheet

to

Dim ws As Worksheet
Dim wsINV As Worksheet

 

[Pudsters]

Its the exact same code, except it has quotes around the range names!

 

[SkipVought]

That’s not what I asked for.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Paste this into your module and run this test, please

Sub sendtimecardTEST()

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next
    
Debug.Print wsINV.Range("RANGE_CUSTNAME")
Debug.Print wsINV.Range("RANGE_INVOICENUMBER")
End Sub

Your values should print in the Immediate Window.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Pudsters]

I also did this just like you did above:

Debug.Print wsINV.Range("RANGE_CUSTNAME")
Debug.Print wsINV.Range("RANGE_INVOICENUMBER")

And it didn't work. Got the same error

But when I changed wsINV to Worksheets("Invoice") it worked and printed out values to the Immediate window

 

[Pudsters]

I copied your code, the same thing - the same error message

Here it is, same as yours:

Sub sendtimecardTEST()

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next
    
Debug.Print wsINV.Range("RANGE_CUSTNAME")
Debug.Print wsINV.Range("RANGE_INVOICENUMBER")
End Sub

 

[Pudsters]

vba is so frustrating

 

[Pudsters]

Is it possible if there is code somewhere else in my workbook setting the Invoice to a different name or variable, could that be the problem?

 

[SkipVought]

Then I'd say that your sheet has something strange going on.

Please upload your workbook of at least the sheet containing the named ranges and your VBA code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[DaveInIowa]

Going back to your original post

I tried something like this thinking Invoice is the codename whether it is Invoice or Invoice 1

It's been a while since I've worked in VBA but I would think you would want to work with the CodeName which is the name of the worksheet that is not in parenthesis in the Project - VBAProject panel. I would rename Sheet1 to something like InvoiceSheet and then reference it that way

Public Sub Test()
    Dim ws As Worksheet
    Set ws = InvoiceSheet
    ws.Cells(5, 5) = "hello, world"
    ' or just...
    InvoiceSheet.Cells(5, 5) = "hi, world"
End Sub

 

[Andrzejek]

Since you stated: "[Invoice Sheet] is always the FIRST worksheet!", try this:

Sub sendtimecardTEST()

    Dim i As Integer
    Dim ws As Worksheet  [green]', wsINV As Worksheet[/green]
    
    Set ws = ThisWorkbook.Sheets(1) 
[green]
    'For Each ws In ThisWorkbook.Worksheets
    '    Select Case ws.Name
    '        Case "Invoice", "Invoice1", "Invoice 1"
    '            Set wsINV = ws
    '            Exit For
    '    End Select
    'Next
    [/green]
Debug.Print ws.Range("RANGE_CUSTNAME")
Debug.Print ws.Range("RANGE_INVOICENUMBER")

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[Pudsters]

I tried that, didn't work: I got error: Method 'Range' of Object'_Worksheet' failed

Sub sendtimecardTEST()

Dim i As Integer
Dim ws As Worksheet ', wsINV As Worksheet

Set ws = ThisWorkbook.Sheets(1)

'For Each ws In ThisWorkbook.Worksheets
' Select Case ws.Name
' Case "Invoice", "Invoice1", "Invoice 1"
' Set wsINV = ws
' Exit For
' End Select
'Next

Debug.Print ws.Range("RANGE_CUSTNAME")
Debug.Print ws.Range("RANGE_INVOICENUMBER")

End Sub

 

[Pudsters]

Here is an idea:

Since my code only seems to accept the 2 naming conventions below,

Worksheets("Invoice").Range("") or Worksheets("Invoice 1").Range("")

Couldn't I just turn this into an Or statement?

If Worksheets("Invoice") is present, send to ("Invoice"). If Worksheets("Invoice 1") is present, send to ("Invoice 1")?

I would think that would work, but I don't know the correct syntax.