Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reference a worksheet name that can change 2

Status
Not open for further replies.
Pudsters

Pudsters

Technical User
Mar 16, 2006
151
US


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")​

 
Sort by date Sort by votes
Hi,

Under what circumstances does the sheet name change?

Does the sheet name ALWAYS begin with Invoice?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote

Yes it always begins with Invoice. The workbook allows the user to open up new invoices and other named worksheets. If they open up new Invoice worksheets, Invoice changes to Invoice 1, and each new invoice becomes Invoice 2, Invoice 3 and so on. I only need to reference the MAIN invoice, which will be either Invoice or Invoice 1.
 
Upvote 0 Downvote
please post the entire code that you're using.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
It's actually based on my last post:

Code: 
Sub sendInvoice()

    Dim i As Integer
    Dim ws As Worksheet
        
    'Set ws = ThisWorkbook.Sheets("Sheet1") - This didn't work!
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        
        .Navigate "[URL unfurl="true"]http://website.com"[/URL] 'change name!
        
        
        Do
            DoEvents
        Loop Until .ReadyState = 4     
         
        With .Document
            For i = 1 To 9
                If Not .getElementById("checkboxIdj" & i).Checked Then
                
                    .getElementById("checkboxIdj" & i).Checked = True                    
                    .getElementById("invoice-j" & i).Value = Worksheets("Invoice").Range("A1")
                    .getElementById("customername-j" & i).Value = Worksheets("Invoice")."B1")
                    
                                     
                    Exit Sub
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
 
Upvote 0 Downvote
Code: 
Private Sub sendtimecard()
    Dim i As Integer
    Dim ws As Worksheet, [b]wsINV As Worksheet[/b]
        
    [b]For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next[/b]
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .Navigate "[URL unfurl="true"]http://website.html"[/URL]
        
        Do
            DoEvents
        Loop Until .ReadyState = 4
        
        With .Document
            For i = 1 To 3
                If Not .getElementById("chkboxJOB" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById("txtNameJOB" & i).Value = [b]wsINV[/b].Range("A1")
                    .getElementById("txtHoursJOB" & i).Value = [b]wsINV[/b].Range("B1")

                    GoTo Xit
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
    
Xit:
    Set wsINV = Nothing
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote

Is it always the first (second? third?) worksheet in your workbook?

If [blue]second[/blue] (for example), you may try:

Code: 
Set ws = ThisWorkbook.Sheets([blue]2[/blue])

---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote


It is always the FIRST worksheet!

I get "Object Variable or With Block Variable not set" error at this line:

.getElementById("txtNameJOB" & i).Value = wsINV.Range("A1")
 
Upvote 0 Downvote
Please post your entire code.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
My code is now showing named ranges, but I had tried it with just ("A1") and ("B1")as well.
Code: 
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"
                Exit For
        End Select
    Next
       
    With CreateObject("InternetExplorer.Application")
        .Visible = True
               
        .Navigate "[URL unfurl="true"]www.webpage.com"[/URL] 'THIS IS LOCAL TEST SITE ONLY!
               
        Do
            DoEvents
        Loop Until .ReadyState = 4     
              
        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)
                                     
                    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
 
Upvote 0 Downvote
Put QUOTES around RANGE_CUSTNAME and RANGE_INVOICENUMBER

Or...
Code: 
‘
                    .getElementById(txtNameJOB" & i).Value = [RANGE_CUSTNAME]
                    .getElementById("invoiceJOB" & i).Value = [RANGE_INVOICENUMBER]
Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote

Quotes don't work, I tried your 2nd option with the brackets, but it returns the cell reference instead of the cell value
 
Upvote 0 Downvote
Code: 
'
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                [highlight #FCE94F]Set wsINV = ws[/highlight]
                Exit For
        End Select
    Next

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote

I don't know how that wasn't showing up above, but it is in my code. Sorry about that, still getting that same error message: Object Variable or With Block Variable not set

So far, it only works if I use:

.Value = Worksheets("Invoice").Range(RANGE_INVOICENUMBER) or: .Value = Worksheets("Invoice 1").Range(RANGE_INVOICENUMBER)
 
Upvote 0 Downvote
Please post your code again.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Here it is, with the code that works for single Invoice commented out:

Code: 
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://www.test.com"[/URL] 'THIS IS LOCAL TEST SITE ONLY!
               
        Do
            DoEvents
        Loop Until .ReadyState = 4   
       
        
        With .Document
            For i = 1 To 9
                If Not .getElementById("checkboxIdj" & 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
 
Upvote 0 Downvote
Your code is missing a QUOTE
Code: 
 .getElementById([b][highlight #FCE94F]"[/highlight][/b]txtNameJOB" & i).Value = wsINV.Range(RANGE_CUSTNAME)

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote

Yeah, I made an edit in the post, it is there in my vba though!
 
Upvote 0 Downvote
Well....???

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
I made an edit in the post" - you cannot do that, please.

When Skip asks you to "Please post your code ", please - Copy your code and Paste it in here.
Do not edit anything.

Otherwise we will play a guessing game with no end.


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

M
  • Locked
  • Question
Reference Pivot Tables by name 1
Replies
2
Views
324
mintjulep
M
K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
343
combo
combo
kapok1
  • Locked
  • Question
how to use vba to forbid "Worksheet Menu Bar" being selected
Replies
6
Views
254
kapok1
kapok1
MattGreer
  • Locked
  • Question
Excel - Copy Filtered Rows from a table to a new blank worksheet
Replies
7
Views
201
combo
combo
sal21
  • Question
scrping in listbox in a web page
Replies
0
Views
229
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top