muthill001
01-13-2007, 04:44 AM
Here?s what I want to do:
Let?s let a ?room? be representative of a1:z33 on sheet 2
Right now this is what my ridiculously miniscule understanding of VBA has allowed me to do:
A user will put the number of ?rooms? he or she wants created in a cell on sheet 1?in the attached worksheet that cell is d2 on sheet 1.
-an event will trigger the creation of the ?rooms? (either a command button or change event; not sure which one yet, but either one would work I think
A user will then be able to add a ?room? through a command button click, which will copy a1:z33, find the last row containing data and paste the information in a1:z33--essentially creating an additional ?room.?
Here?s where I need help
The problem I?m having is that I want to account for if a person initially wants to create 30 rooms, fills in some information in those rooms, but then realizes that they need 40 rooms (or some other arbitrary number). Instead of them having to just add a room via the command button, I would like for them to be able to change the number of rooms they entered in d2 of sheet 1 and have the macro add the correct number of rooms to sheet 2 without deleting the pre-existing rooms: so find the last row with data, move down one and add the appropriate number of rooms.
The rooms will always take up the same number of rows and columns so I thought I could have my loop event terminate when the number of rows used in sheet 2 matches the number in d2 of sheet 1 * 33(which is the number of rows a room is comprised of).
So something like: so if d2 equals 2, when the trigger occurs 2 rooms or 66 rows should be filled with data. But if the user changes d2 to 3, when the trigger occurs the macro should just add one room starting from 67
Also, if a person starts out with 30 rooms, enters some data and then realizes they only want 29 rooms, I would like for when they change d2 from 30 to 29 for the macro to remove the last room on sheet 2 without having to just start over and create 29 instances of the ?room??so again making the macro match up the number of rows used on sheet 2 with the number in d2 on sheet 1 * 33
Thanks in advance for your time and help with this.
Let?s let a ?room? be representative of a1:z33 on sheet 2
Right now this is what my ridiculously miniscule understanding of VBA has allowed me to do:
A user will put the number of ?rooms? he or she wants created in a cell on sheet 1?in the attached worksheet that cell is d2 on sheet 1.
-an event will trigger the creation of the ?rooms? (either a command button or change event; not sure which one yet, but either one would work I think
A user will then be able to add a ?room? through a command button click, which will copy a1:z33, find the last row containing data and paste the information in a1:z33--essentially creating an additional ?room.?
Here?s where I need help
The problem I?m having is that I want to account for if a person initially wants to create 30 rooms, fills in some information in those rooms, but then realizes that they need 40 rooms (or some other arbitrary number). Instead of them having to just add a room via the command button, I would like for them to be able to change the number of rooms they entered in d2 of sheet 1 and have the macro add the correct number of rooms to sheet 2 without deleting the pre-existing rooms: so find the last row with data, move down one and add the appropriate number of rooms.
The rooms will always take up the same number of rows and columns so I thought I could have my loop event terminate when the number of rows used in sheet 2 matches the number in d2 of sheet 1 * 33(which is the number of rows a room is comprised of).
So something like: so if d2 equals 2, when the trigger occurs 2 rooms or 66 rows should be filled with data. But if the user changes d2 to 3, when the trigger occurs the macro should just add one room starting from 67
Also, if a person starts out with 30 rooms, enters some data and then realizes they only want 29 rooms, I would like for when they change d2 from 30 to 29 for the macro to remove the last room on sheet 2 without having to just start over and create 29 instances of the ?room??so again making the macro match up the number of rows used on sheet 2 with the number in d2 on sheet 1 * 33
Thanks in advance for your time and help with this.