Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to get a macro to do what i want?
I have designed a form in Excel and the data that is inputted i want to be
able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. |
#2
|
|||
|
|||
Calculate the last row
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row if iLastRow < 1 Or Range("A1")<"" Then iLastRow = iLastRow + 1 End If then use this in the data placement Cells(iLastRow,"A").Value = myValue etc. -- HTH RP (remove nothere from the email address if mailing direct) "Carrie" wrote in message ... I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. |
#3
|
|||
|
|||
Thank you for that information but i do not understand the last bit about
Data Placement. Do i need to write that within the marco? If so then is it underneath the End If? Also you wrote calculate the last row, do i need to write that into the macro? Sorry about being a pain! Carrie "Bob Phillips" wrote: Calculate the last row iLastRow = Cells(Rows.Count,"A").End(xlUp).Row if iLastRow < 1 Or Range("A1")<"" Then iLastRow = iLastRow + 1 End If then use this in the data placement Cells(iLastRow,"A").Value = myValue etc. -- HTH RP (remove nothere from the email address if mailing direct) "Carrie" wrote in message ... I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. |
#4
|
|||
|
|||
The first bit shows you have to calculate the next free row by identifying
the last filled row. Somewhere you mast have some code that copies the data to a worksheet. I haven't seen the code, but maybe something like Range("A1").Select Selection.Formula = "ABC" where ABC may be a variable, or another range, or just a string. I am suggesting replacing that with Range("A" & iLastRow).Value = "ABC" -- HTH RP (remove nothere from the email address if mailing direct) "Carrie" wrote in message ... Thank you for that information but i do not understand the last bit about Data Placement. Do i need to write that within the marco? If so then is it underneath the End If? Also you wrote calculate the last row, do i need to write that into the macro? Sorry about being a pain! Carrie "Bob Phillips" wrote: Calculate the last row iLastRow = Cells(Rows.Count,"A").End(xlUp).Row if iLastRow < 1 Or Range("A1")<"" Then iLastRow = iLastRow + 1 End If then use this in the data placement Cells(iLastRow,"A").Value = myValue etc. -- HTH RP (remove nothere from the email address if mailing direct) "Carrie" wrote in message ... I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for multiple charts | Excel Worksheet Functions |