CODEWITHSUNDEEP
vbaexcel
TheCharlatan
TheCharlatan

Reputation: 290

Click Button on One Sheet for Data on another Sheet

I have a click button on "sheet1" and would like it to execute its code when clicked on "sheet2", where all the data is at. What is the recommended way to activate the other sheet?

I am new to vba and have read some posts about this in the past few days, but have not found a satisfying answer so far on how to implement the sheet change as simple as possible. I have seen things like:

Private Sub CommandButton1_Click()
    Worksheets("sheet2").Cells(1, 1).Value = 1 
    Worksheets("sheet2").Cells(2,1).Value = 1
    ...
End Sub

, where I have to constantly redeclare the object's scope to "sheet2".

Upvotes: 0

Views: 2906

Answers (2)

cyboashu
cyboashu

Reputation: 10443

you should use With and Activate or Select will bring the sheet in focus.

Private Sub CommandButton1_Click()
   With  Worksheets("sheet2")
      .Cells(1, 1).Value = 1 
      .Cells(2,1).Value = 1
    ...

    '/Activate the sheet
     .Activate
    End With

End Sub

Upvotes: 1

Ahmad
Ahmad

Reputation: 12737

You could switch to sheet2 and then execute any subsequent command without the need to explicitly mentioned sheet2 like this:

Private Sub CommandButton1_Click()
    Worksheets("sheet2").Activate    'Switch to sheet2

    Cells(1, 1).Value = 1    'this change will happen in sheet2
    Celss(2,1).Value = 1     'this too
    ...
End Sub

Upvotes: 0

Related Questions