Worksheet as a variable

[RogueCode]

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

 

[Marcelo Branco]

Maybe...

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.

 

[RogueCode]

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

 

[Marcelo Branco]

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.

 

[RogueCode]

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

 

[Marcelo Branco]

Well done!

M.

 

[Rick Rothstein]

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...

[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)...

[B][COLOR=#0000ff]Cells(Rows.Count, "A")[/COLOR][/B]

 

[RogueCode]

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!