Worksheet Variable

[Xenocity]

HI,

I have tried looking at all the examples of how to create a formula in VBA that has the worksheet defined as a variable that I can easily change for each report I need.

I don't know how to define this at the top of my module.

Range("B486").Select
ActiveCell.FormulaR1C1 = _"=IF('2012master'!R[-483]C[82]="""","""",VLOOKUP(""Y"",'2012master'!R[-483]C[82]:R[-483]C108,24,FALSE))"

I am looking for something at the top similar to:

set ws = '2012master'

so that all the formulas on the vba will use the workshee that I define.

Secondly, I wanted to make the process easier for the user so that they could choose the worksheet from a user form to populate the specific worksheet being used for the variable if this is possible.

Thank you in advance.

 

[Xenocity]

One last question, Can I hide any of the worksheet from not populating the combo box, it would only be 4 of the sheets. Not a problem just more aesthetically pleasing.

Thank you again Ruddles

 

[Ruddles]

Instead of this:-

    UserForm1.ComboBox1.AddItem ws.Name

do this:-

  select ws.name
    case "this_sheet", "this_other_sheet"
    [COLOR=green]  ' do nothing[/COLOR]
    case else
      UserForm1.ComboBox1.AddItem ws.Name
  end select

 

[Xenocity]

That worked as well.

If anyone else is looking I had to use:

Select case ws.name

in order for it to function but after that it worked!

Thanks

 

[Ruddles]

Oopsie!