PLEASE help with cut/paste macro
 

I have two worksheets: "residential" and "blanktables"

"Residential" has a 27-row form people fill out. The user fills out
one form per student.

Since most users have many students, I'm trying to make a button on
the last line of the form so that when the user clicks it, it
executes a macro that inserts a new, blank form for another student.

The macro goes to "blanktables" and selects an empty blank form;

Returns to "Residential" and pastes a new blank form below the first
one (the first row below the button). There's a button on the bottom
of the new blank form running the same macro, in case the user wants
to add another form below that one. Then another one below that one,
and so on. Some users have over 100 students.

At first I just created the button and performed the task using
"Record New Macro." It worked fine. BUT every time the user clicks the
button on the NEW form, it pastes it in the same place, over and over.
How can I keep it from doing this? It needs to paste it on the row
below the button, no matter where the button is.

The recorded macro looks like this (the button is on A30):

Sheets("blanktables").Select
Range("A1:B29").Select
Selection.Copy
Sheets("Residential").Select
Range("A31").Select
ActiveSheet.Paste

Please reply to the newsgroup.
THanks a heap,
Ron M.

PLEASE help with cut/paste macro
 

If you have a value in the bottom row of column A of the form, then this
should do it....otherwise the offset will have to be adjusted......

Sub AddForm()
Sheets("blanktables").Select
Range("A1:B29").Select
Selection.Copy
Sheets("Residential").Select
Range("a65000").Select
Selection.End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

Vaya con Dios,
Chuck, CABGx3



" wrote:

I have two worksheets: "residential" and "blanktables"

"Residential" has a 27-row form people fill out. The user fills out
one form per student.

Since most users have many students, I'm trying to make a button on
the last line of the form so that when the user clicks it, it
executes a macro that inserts a new, blank form for another student.

The macro goes to "blanktables" and selects an empty blank form;

Returns to "Residential" and pastes a new blank form below the first
one (the first row below the button). There's a button on the bottom
of the new blank form running the same macro, in case the user wants
to add another form below that one. Then another one below that one,
and so on. Some users have over 100 students.

At first I just created the button and performed the task using
"Record New Macro." It worked fine. BUT every time the user clicks the
button on the NEW form, it pastes it in the same place, over and over.
How can I keep it from doing this? It needs to paste it on the row
below the button, no matter where the button is.

The recorded macro looks like this (the button is on A30):

Sheets("blanktables").Select
Range("A1:B29").Select
Selection.Copy
Sheets("Residential").Select
Range("A31").Select
ActiveSheet.Paste

Please reply to the newsgroup.
THanks a heap,
Ron M.

PLEASE help with cut/paste macro
 

if you want the macro to paste it on the same sheet but below the current
"form" then instead of defining the range("a31") as your paste location you
have to make the macro search for the end of the previous form. this can be
done by using end(xlup).

try this for your code:

Sheets("blanktables").Select
Range("A1:B29").Copy
Sheets("Residential").Select
Copyrow = Cells(65536, 1).End(xlUp).Row + 1
Cells(Copyrow, 1).PasteSpecial


this finds the last cell in column A that has information in it and pastes 1
row beneath that. hence row + 1. so you may have to change it depending on
the layout of your spreadsheet.

hope this helps

-josh

" wrote:

I have two worksheets: "residential" and "blanktables"

"Residential" has a 27-row form people fill out. The user fills out
one form per student.

Since most users have many students, I'm trying to make a button on
the last line of the form so that when the user clicks it, it
executes a macro that inserts a new, blank form for another student.

The macro goes to "blanktables" and selects an empty blank form;

Returns to "Residential" and pastes a new blank form below the first
one (the first row below the button). There's a button on the bottom
of the new blank form running the same macro, in case the user wants
to add another form below that one. Then another one below that one,
and so on. Some users have over 100 students.

At first I just created the button and performed the task using
"Record New Macro." It worked fine. BUT every time the user clicks the
button on the NEW form, it pastes it in the same place, over and over.
How can I keep it from doing this? It needs to paste it on the row
below the button, no matter where the button is.

The recorded macro looks like this (the button is on A30):

Sheets("blanktables").Select
Range("A1:B29").Select
Selection.Copy
Sheets("Residential").Select
Range("A31").Select
ActiveSheet.Paste

Please reply to the newsgroup.
THanks a heap,
Ron M.