Set Worksheet name from Variable?

[kpm30519]

Hi All--

I have a userform that pulls data from 4 different worksheets. I set specific labels to these data values.
Each of the 4 worksheets is set up exactly the same, so Range("C:C") on one is the same on another, so I want to use a single module to run the code 4 times. Sounds simple...

Here's the module:

Sub LastDateEntered()
Label19.Caption = "Last Date Entered"
    ReturnLastDate = Ws.Range("C:C"). _
       End(xlDown).Offset(0, 0).value
           LastDraw1 = Ws.Range("D:D"). _
       End(xlDown).Offset(0, 0).value
           LastDraw2 = Ws.Range("E:E"). _
       End(xlDown).Offset(0, 0).value
           LastDraw3 = Ws.Range("F:F"). _
       End(xlDown).Offset(0, 0).value
           LastDraw4 = Ws.Range("G:G"). _
       End(xlDown).Offset(0, 0).value
           LastDraw5 = Ws.Range("H:H"). _
       End(xlDown).Offset(0, 0).value
    
    
Label1.Caption = ReturnLastDate
Label20.Caption = Last1
Label21.Caption = Last2
Label22.Caption = Last3
Label23.Caption = Last4
Label24.Caption = Last5

When it runs, I get Run Time error 424, Object Required, with the line ReturnLastDate highlighted.

When I run this directly from the userform it works great. It pulls the last values from the respective columns and displays them in the Labels.

On the userform I have this:

Dim Ws As Worksheet
Set Ws = Worksheets("WSName5")

'A whole bunch of stuff happens here, not related.

Label19.Caption = "Last Date Entered"


    ReturnLastDate = Ws.Range("C:C"). _
       End(xlDown).Offset(0, 0).value
           LastDraw1 = Ws.Range("D:D"). _
       End(xlDown).Offset(0, 0).value
           LastDraw2 = Ws.Range("E:E"). _
       End(xlDown).Offset(0, 0).value
           LastDraw3 = Ws.Range("F:F"). _
       End(xlDown).Offset(0, 0).value
           LastDraw4 = Ws.Range("G:G"). _
       End(xlDown).Offset(0, 0).value
           LastDraw5 = Ws.Range("H:H"). _
       End(xlDown).Offset(0, 0).value
    
    
Label1.Caption = ReturnLastDate
Label20.Caption = Last1
Label21.Caption = Last2
Label22.Caption = Last3
Label23.Caption = Last4
Label24.Caption = Last5

I declare the worksheet name on the userform, then call the module. And, the module does not work.

I am stumped. Also, if there is a better way of doing this, I'd appreciate the help.

 

[NdNoviceHlp]

Add this to your sub...

Dim Ws As Worksheet
Set Ws = Worksheets("WSName5")

HTH. Dave

 

[kpm30519]

Add this to your sub...

Dim Ws As Worksheet
Set Ws = Worksheets("WSName5")

HTH. Dave

I have four sheets. Each sheet has a name. I need to run the module for each of the four sheets, and the sheet is selected by the user. If I name the sheet inside the module, It's no better than typing it out four times, I need four different sheet names.

 

[Fluff]

If you change the module title to

Sub LastDateEntered(Ws As Worksheet)

and call it like

Call LastDateEntered(Ws)

The worksheet variable will be passed to the module

 

[kpm30519]

Thank You Fluff!
I hate being a newbie at VBA...

Next question, I'd really like to be able to set the variable Ws by a combobox, but

Set Ws = WorkSheets(ComboBox.value)

doesn't seem to work. Any ideas?

 

[Fluff]

In what way doesn't it work?

 

[kpm30519]

Error 9, Subscript out of range.

 

[Fluff]

In that case you don't have a sheet with the same name as the combo value.
Check spelling & also check for leading/trailing spaces.

 

[kpm30519]

All for want of a MOTHER FREAKING SPACE.

Thanks, Fluff. You're a lifesaver!

 

[Fluff]

You're welcome & thanks for the feedback