Activate Sheet switching

[Imran J Khan]

I need to be able to activate sheets. I have tried sheets.sheet3.activate and
worksheets("sheet3").activate in a couple of places each in the following
code and it does not work. I need to activate alternately between Sheet2 and
Sheet3 to insert data collected in a vb userform. But if I the sheet where
the data is tobe inserted is not on top, I get an error. If I move the sheet
to the top, the macro runs fine.

Private Sub cmdEnter_Click()

Dim LastRow As Object

Set LastRow = Sheet2.Range("a65536").End(xlUp)
LastRow.Offset(-1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0).Value = Date
ActiveCell.Offset(0, 1).Value = "Buy"
ActiveCell.Offset(0, 2).Value = TextBox1.Text
ActiveCell.Offset(0, 3).Value = TextBox2.Text
ActiveCell.Offset(0, 4).Value = TextBox3.Text
ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text
ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text

Set LastRow = Sheet3.Range("PrfrShrs").End(xlDown)
LastRow.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0).Value = Date
ActiveCell.Offset(0, 1).Value = "Buy"
ActiveCell.Offset(0, 2).Value = TextBox1.Text
ActiveCell.Offset(0, 3).Value = TextBox2.Text
ActiveCell.Offset(0, 4).Value = TextBox3.Text
ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text
ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text


Unload Me
End Sub

Imran

 

[Dave Peterson]

I'm not sure if this is what you want--do you want to insert a new line directly
above the last row with data?

This avoids the .selects and the selection. and the activecell stuff:

Option Explicit
Private Sub cmdEnter_Click()

Dim LastCell As Range

With Sheet2
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
LastCell.EntireRow.Insert
With LastCell.Offset(-1, 0)
'and populate the data
.Offset(0, 0).Value = Date
.Offset(0, 1).Value = "Buy"
.Offset(0, 2).Value = TextBox1.Text
.Offset(0, 3).Value = TextBox2.Text
.Offset(0, 4).Value = TextBox3.Text
.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text
.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text
End With
End With

With Sheet3
Set LastCell = .Range("PrfrShrs").End(xlDown)
LastCell.EntireRow.Insert
'come down a row
With LastCell.Offset(-1, 0)
'and populate the data
.Offset(0, 0).Value = Date
.Offset(0, 1).Value = "Buy"
.Offset(0, 2).Value = TextBox1.Text
.Offset(0, 3).Value = TextBox2.Text
.Offset(0, 4).Value = TextBox3.Text
.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text
.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text
End With
End With

Unload Me
End Sub

But I'm not sure this code is putting things where you want them. Test it
against a copy of your worksheets.

 

[Imran J Khan]

Thanks Dave, with a little modification, it worked. And yes, i did want to
insert a new line directly above the last row with data.

 

[Dave Peterson]

Glad it worked.

But it did work without modification for me <vbg>.