Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting data in next row on clicking a button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting data in next row on clicking a button
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting data in next row on clicking a button
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 "Patrick Molloy" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a picture object and double-clicking on it opens photo e | Excel Discussion (Misc queries) | |||
Passing parameters from clicking a button | Excel Programming | |||
Get info on toolbar button by clicking on it | Excel Programming | |||
Store data JUST entered in Activecell after clicking command button on worksheet | Excel Programming |