Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut and Paste Macro | Excel Discussion (Misc queries) | |||
cut and paste macro | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
cut and paste macro? | Excel Discussion (Misc queries) |