Worksheet as a variable

RogueCode

New Member
Joined
Aug 5, 2014
Messages
30
G'day Gurus,

Is there a way to set the blue text in this as a variable? I have 3 tabs where I want to add the same info (Race 1, Race 2, Race 3) and i want the one user form to add info to all when you click on the "Add New" Command Button.

Private Sub cbAddNew_Click()

Set LastRow = Worksheet("Race 1").Range("A65536").End(xlUp)
With LastRow
.Offset(1, 0).Value = txtRank
.Offset(1, 1).Value = TxtName
End With

End Sub

TIA
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Maybe...

Code:
Private Sub cbAddNew_Click()
    Dim sh As Variant, LastRow As Range
    
    For Each sh In Array("Race 1", "Race 2", "Race 3")
        Set LastRow = Worksheet[COLOR=#ff0000]s[/COLOR](sh).Range("A65536").End(xlUp)
        With LastRow
            .Offset(1, 0).Value = TxtRank
            .Offset(1, 1).Value = TxtName
        End With
    Next sh


End Sub

Hope this helps

M.
 
Upvote 0
Close. But I only want it to add to the sheet specified.

e.g. If I select "Race 1" from the list it will put it on Race 1 only, If I select "Race 2" from the list it will put it on Race 2 only, etc
 
Upvote 0
Close. But I only want it to add to the sheet specified.

e.g. If I select "Race 1" from the list it will put it on Race 1 only, If I select "Race 2" from the list it will put it on Race 2 only, etc


You wrote

Is there a way to set the blue text in this as a variable? I have 3 tabs where I want to add the same info (Race 1, Race 2, Race 3) and i want the one user form to add info to all when you click on the "Add New" Command Button.

Which list? You have not mentioned a list.

M.
 
Upvote 0
Got it to work using this:

Private Sub cbAddData_Click()
Dim sh As String


If cboTabName.Value = "Race 1" Then
sh = "Race 1"
ElseIf cboTabName.Value = "Race 2" Then
sh = "Race 2"
ElseIf cboTabName.Value = "Race 3" Then
sh = "Race 3"
End If


Set LastRow = Worksheets(sh).Range("A65536").End(xlUp)
With LastRow
.Offset(1, 0).Value = txtRank
.Offset(1, 1).Value = txtName
End With
 
Upvote 0
Got it to work using this:

Private Sub cbAddData_Click()
Dim sh As String


If cboTabName.Value = "Race 1" Then
sh = "Race 1"
ElseIf cboTabName.Value = "Race 2" Then
sh = "Race 2"
ElseIf cboTabName.Value = "Race 3" Then
sh = "Race 3"
End If



Set LastRow = Worksheets(sh).Range("A65536").End(xlUp)
With LastRow
.Offset(1, 0).Value = txtRank
.Offset(1, 1).Value = txtName
End With

If you think about it for a little bit, and assuming your ComboBox only contains valid item, it should become obvious why the following single line of code can replace the 7 lines of code I highlighted in red above...
Code:
[COLOR=#ff0000][B]sh = cboTabName.Value[/B][/COLOR]

As for what I highlighted in blue above, using this instead would be better (then your code would be guaranteed to work in any version of Excel)...
Code:
[B][COLOR=#0000ff]Cells(Rows.Count, "A")[/COLOR][/B]
 
Last edited:
Upvote 0
Hi Rick,

I did think of it, but much later! Thanks I will make the appropriate changes to the code!

Marcelo, you're right! My bad!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top