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]

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

What this tells me is either 1) your Named Ranges are not on the first sheet or you have no Named Ranges at all!

This actually works by writing the values in the Immediate Window (ARE YOU LOOKING THERE?)

Set ws = ThisWorkbook.Sheets(1)

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

Debug.Print [RANGE_CUSTNAME]
Debug.Print [RANGE_INVOICENUMBER]

NOTICE, the beauty of Named Ranges is that you don't even need a Worksheet reference!!! BOTH of the above techniques work!

So what you have told us, AGAIN, ain't so!

Please upload your workbook. Seems you have something screwy going on.


Skip,

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

 

[Pudsters]

Forget the named ranges, it doesn't even work, when I use ("A1") and ("B1") instead of named ranges.

 

[SkipVought]

Please upload your workbook. With a quick glance I can tell you what’s wrong.

Skip,

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

 

[Andrzejek]

vba is so frustrating

It is like saying: “Driving standard / manual transmission car is so frustrating.” Every time you start from dead stop, the engine dies. Driving in first gear the car jumps and shakes, passengers get sick. You need to replace the clutch every few thousand miles. RPMs go to a red zone on highway driving at 70 mph in second gear, the engine screams.

People who know how to drive stick shift cars don’t have any of these problems and LOVE it.

The same goes with VBA



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Twenty four years ago Excel VBA was frustrating.

Then a knowledgeable guy sat down with me and gave me a boost. I got a little better.

Seven years later I came across Tek-Tips and began getting a lot of boosts. Still do.

Excel VBA is an amazing tool. But, like anything worth doing, it takes work. Constant work. If you’re gonna just dabble here and there, VBA will, without a doubt, continue to be frustrating. If you put your mind and efforts toward developing a growing level of proficiency, you will be richly rewarded.

Skip,

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

 

[Pudsters]

Let me rephrase: VBA can be frustrating when you only have a very limited understanding of it (beginning users).

Thanks for all of your help

 

[Andrzejek]

That's where you have other people (i.e. Tech-Yips) to help you

Did you make it work?
If so, could you post the final, working version of your code to benefit others who may look for solution?



---- Andy

There is a great need for a sarcasm font.

 

[Pudsters]

Still working on it, but I have to put it on hold for a few days, will post back the results. Thanks again!

 

[SkipVought]

Good. Don’t give up. You have help at hand.

I see you’re from PA. Grew up near Philly. Got my degree from LU in Bethlehem. Married a girl from Williamsport. First job in Pittsburgh. Just visited my sister in Harrisburg last week.

Don’t give up. We’ll figure this out together.

Skip,

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

 

[Pudsters]

I DID IT! I'M SO HAPPY, IT NOW WORKS PERFECTLY!

Someone else created the original workbook, and it is really large! I found this function inside a module to determine the sheetname:

Function GetMainInvoiceSheetName() As String

   With wkbCall
     If .Worksheets("wksInternal").Range("Active_Invoices").Value > 1 Then
       GetMainInvoiceSheetName = "Invoice 1"
     Else
       GetMainInvoiceSheetName = "Invoice"
     End If
   End With
End Function

So I added this to the top of my sub:

Dim ws As Worksheet
Set ws = wkbCall.Worksheets(GetMainInvoiceSheetName)

And changed all my references to:

.Value = ws.Range(RANGE_INVOICENUMBER) and so on...​

Thank you for all your help and encouragement!

Oh, and Skip, I'm in Allentown suburbs, about 10 minutes from Lehigh U!

 

[SkipVought]

Way, way back, some 60 years ago, my family used to drive up to Atown, to a restaurant, maybe it was a smorgasbord, that had “hot bacon dressing”. Of course often flew into ABE. So although I’ve been in TX nearly forty years, I still consider myself a citizen if Penn’s Woods. Got a nephew that works in Atown.

BTW, if this works...

.Value = ws.Range(RANGE_INVOICENUMBER)

...without quotes, then RANGE_INVOICENUMBER is a variable and not a Named Range. Somewhere logically prior to this statement, you must have an assignment of a string range reference to that variable ([tt]RANGE_INVOICENUMBER = someRangeRef[/tt])

Glad you got that sorted out.


Skip,

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

 

[Pudsters]

Skip, you are right. I didn't realize that named ranges and variables assigned to a range were two different things, which guess explains the lack of quotes.

 

[Andrzejek]

Pudsters,
If anybody ever deserved a Star for the help provided, you should award one to Skip.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Let me just give you a few pointers regarding Named Ranges.

I find Named Ranges to be extremely helpful on the sheet. When used in formulae, it almost makes it self documenting. What’s easier to understand?:
[tt]
=MATCH(SelectedEmp,EmpList,0)
=MATCH(Sheet2!A1,A2:A127,0)
[/tt]

A Named Range can reference a single cell or an other range, like a column of data.

You’ll notice the Name Box in the upper left-hand corner just above the sheet grid. If you hover your mouse pointer over it, you’ll see the name of this feature. It has a drop down that displays any Named Ranges on the sheet or at the workbook level, and it displays the ActiveCell reference.

Do some exploring in Formulas > Defined Names…, particularly Create From Selection. This is VERY useful.

Structured Tables also used named ranges that are a built-in feature. This is VERY VERY useful!!!

Skip,

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