inserting data in next row on clicking a button

[sam]

Hi All,

How can I loop through excel rows by clicking a button. I want to populate a
new row in excel every time I click this button.

For eg: I Click a button "Insert" , on clicking this button I want to insert
data in next empty row. Once user inputs data on Sheet1 and clicks insert, it
inserts the data in sheet2 and clears the data in sheet1, Now user can again
input data in sheet1 and clicking insert will insert data in row 2 of sheet2
(Basically next available row).

Is there a way to loop it like this? and keep updating the sheet with data
in next row?
Here is the part of code for looping through new rows. But it doesnt work as
I want it to.


Dim r as long

Set wst = Worksheets("sheet1")

With wst
..Range("A1").Value = Me.City.Value

End With

r = r + 1

FName = "C:\My Documents\" _
& "Address" & ".xls"

wst.SaveAs FileName:=FName

Hope I made it clear.

Thanks in Advance

 

[Patrick Molloy]

so sheet1 is userinput and what the user enters goes into one row in sheet2?
you don't say how the data is arranged in sheet1, so i give just a couple of
examples here

'get next available row of sheet2
nextrow = worksheets("sheet2").Range("A1").End(xlDown).Row + 1
' copy the data from sheet1 - examples
worksheets("sheet2").Cells(nextrow,"A").Value = _
worksheets("sheet1").Range("A1").Value
worksheets("sheet2").Cells(nextrow,"C").Resize(,3).Value = _
worksheets("sheet1").Range("A5:C5").Value
'clear source
worksheets("Sheet1").Cells.ClearContents

 

[sam]

Hey Patrick, Thank you for the help.

I am sorry, But I forgot to metion that the Userform I launch is launched
from Sheet1, Which asks for certain inputs and clicking insert on that
userform will insert the data in Sheet2. So I would need a VBA to make this
to work.

Also Is it possible that, Clikcin Insert on Userfrom asks me to open a
different workbook and once I select a workbook It populates the Sheet1 of
that workbook? and also inserts in a new row everytime I click Insert?

Sorry for the confusion earlier, I should have mentioned this before

Thanks in advance