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.

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.

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.

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.