PDA

View Full Version : Solved: row counting, pasting on trigger



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.

tstom
01-13-2007, 06:38 AM
Nevermind. I should have looked at your attachment first...

XLGibbs
01-13-2007, 08:15 AM
I would do it all in one button (The add button). This works for me to properly either remove or add required rooms without doing it from scratch each time



Private Sub addbtn1_Click()

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim lpt As Integer, counter As Integer
Dim rngRoomCopy As Range, rngRoomPaste As Range, rngCheckFirst
Dim x As Integer, lastRoomCount As Long
lpt = Worksheets("Sheet1").Range("d2").Value - 1

lastRoomCount = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

If lastRoomCount > 33 Then
lastRoomCount = lastRoomCount / 33
Else
lastRoomCount = 1
End If

Set rngRoomCopy = Sheets(2).Range("A1:Z33")
If lastRoomCount < lpt Then
For z = (lastRoomCount + 1) To lpt Step 1
Set rngRoomPaste = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
rngRoomCopy.Copy rngRoomPaste
Next z
End If

If lastRoomCount > lpt Then
Sheets(2).Cells((lpt * 33) + 1, 1).Resize(lastRoomCount * 33, 26).Delete
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

muthill001
01-13-2007, 12:08 PM
XL Gibbs

wow, thanks so much


I think that is going to work perfectly, and so much prettier than I would have tried. I think what I’ll do is change the counter value to allow for one roommore than what the user puts in and then Hide the first room so that the pasted rooms are always blank.

Do you foresee major issues tweaking this code to make it so I can do the same thing on multiple sheets at the same time?

again, thank you.

XLGibbs
01-13-2007, 03:26 PM
Nope, you can just use


Dim wsDest as Worksheet

Set Sheets("Sheet2") 'or sheet3 etc...

then refer to wsDest in the copy room routine. So that wsDest referst to which ever sheet you want to paste to.

Instead of just hiding the first one, you can just store it on a hidden sheet or an unused part of the worksheet. You might have to adjust the comparisons I used though...

Or you can just clear the contents of the dimensioned range and remove cells values but retain formatting

lucas
01-14-2007, 11:48 AM
Using Pete's code....doesn't copy to mult sheets but it does use your sheet 2 as a template to populate sheet Results

muthill001
01-14-2007, 06:35 PM
thanks everyone
it's working great so far. i've opted to just hide the first instance of room on each individual sheet just becuase it's easier for me to keep myself organized.

Another issue has come up and it is this:

i've figured out how to set the print area on a given sheet from the first cell with stuff in it to the last cell that has stuff in it, but how do i add a page break after every room that is added so that if there are 2 rooms, there will be 1 room per printed page?

thanks in advance.

muthill001
01-15-2007, 01:34 AM
thanks everyone
it's working great so far. i've opted to just hide the first instance of room on each individual sheet just becuase it's easier for me to keep myself organized.

Another issue has come up and it is this:

i've figured out how to set the print area on a given sheet from the first cell with stuff in it to the last cell that has stuff in it, but how do i add a page break after every room that is added so that if there are 2 rooms, there will be 1 room per printed page?

thanks in advance.

Edit:: i think for now i've figured out a way to add the pagebreaks by adding this code into the For...Next loop:

rngRoomPaste.pagebreak = xlPageBreakManual

which i'm pretty sure just finds the last used row after each pasting, offsets down one row, and inserts a page break. am i understanding how that code works correctly?