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" wrote:
> 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 wrote:
> >
> > 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
>
|
Similar Threads
