PDA

View Full Version : [SOLVED] Advice on adding a row



TButhe
07-06-2005, 11:20 AM
Hi All,
I have several spreadsheet forms that will eventually be placed out as templates for the employees at our branch facilities. These forms will vary in the number of rows of data that will be needed by each facility. How do you handle it when you want to insert a row and the copy the formulas and formatting from the row above or even below. I have tried several methods and none are working well. I can get the row inserted but cannot seem to get the formulas and formatting copied up or down reliably. I tried using this KB article but it didn't work, well it worked but not for this example. http://www.vbaexpress.com/kb/getarticle.php?kb_id=521&PHPSESSID=ecfaffe07af3fde21726c6555b57259e I have to protect these forms and this just didn't seem like the way to go. It just seems that there has to be a better way than having an enormous amount of rows just to cover everyone. Anyway, here is an examle file - I got some help on this yesterday too so if it looks familar that's why. Any help is more than appreaciated. :help
Hope I have been clear if not just let me know. THANKS!!!!!:friends:

austenr
07-06-2005, 12:22 PM
I am not clear what you are looking for but may I make a suggestion about your form. You might want to put a numeric check in fields that the user inputs data into. For ex. put a check for a numeric entry only in the current balance field. This saves lots of headaches down the road.

TButhe
07-06-2005, 01:02 PM
I guess what I am concerned with is that the users NEVER enter their data in the correct date order so i want to make sure that they can add a row where ever they need to. (The "owner" of the forms wants both a sort button and a button to be able to insert a row) The sort button helps but I really don't want a form that has formulas all the way down to the bottom ... Do I?? maybe I do. :dunno I just really struggle with the not being able to predict how much data each facility will need to input. I just want a macro that inserts a row and then copies the format and formulas from the row above or below (whichever) and pastes it to the new row. I have come up against this in several projects and can't figure it out. It just doesn't seem to work the way I have it now.

austenr
07-06-2005, 01:09 PM
Why not just record a macro to insert a row, copy down the row above, and delete the cells with data. Assign it to a button and just click on it as many times as you need new row(s).

TButhe
07-06-2005, 01:25 PM
When I do that - then my formulas get messed up. If you look at the formula in column G - it takes the total of the cell above it and adds or subtracts. When I copy the formula down it doesn't get the formula right - it adds column g of the current row instead of the row above it. It is weird - it works great manually. Seems to work ok the first time but not so good if you try to insert two rows. I also need to have the data cleared out so was trying to just paste the formats and formulas but that was sorta over my head - like a lot of this stuff. I don't know maybe it's my version or something. It seems like this shouldn't be so hard but it is eluding me.....:stars:

Anyway, thanks for the quick response.

TButhe
07-11-2005, 08:34 AM
Here's what I ended up with in case anyone was wondering.



Sub myinsrow()
Dim Row As Integer
Application.ScreenUpdating = False
If ActiveCell.Row < 9 Then
Dim myCell As String
myCell = MsgBox("You cannot add a row above this row. Please choose an _
alternate location to insert a row.", vbOKOnly)
If myCell = vbOKOnly Then
Exit Sub
End If
Else
ActiveSheet.Unprotect Password:="cfs"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
End If
End Sub

Had to copy formulas down separate from copying the row itself since the formulas referred to the copied row. At least I think that is why it didn't work to just copy it down. :sigh:

It is working now so I guess i should be on :cloud9: . Thanks and have a great week

Bob Phillips
07-11-2005, 09:02 AM
Here's what I ended up with in case anyone was wondering.



Sub myinsrow()
Dim Row As Integer
Application.ScreenUpdating = False
If ActiveCell.Row < 9 Then
Dim myCell As String
myCell = MsgBox("You cannot add a row above this row. Please choose an _
alternate location to insert a row.", vbOKOnly)
If myCell = vbOKOnly Then
Exit Sub
End If
Else
ActiveSheet.Unprotect Password:="cfs"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.ClearContents
End If
End Sub

Had to copy formulas down separate from copying the row itself since the formulas referred to the copied row. At least I think that is why it didn't work to just copy it down. :sigh:

It is working now so I guess i should be on :cloud9: . Thanks and have a great week

This seems to do the same to me (note your MsgBox return value is wrongly handled)



Sub myinsrow()
Dim Row As Integer
Application.ScreenUpdating = False
If ActiveCell.Row < 9 Then
Dim myCell As String
myCell = MsgBox("You cannot add a row above this row. Please choose an " & _
"alternate location To insert a row.", vbOKOnly)
If myCell = vbOK Then
Exit Sub
End If
Else
' ActiveSheet.Unprotect Password:="cfs"
With ActiveCell
.EntireRow.Insert Shift:=xlDown
.Offset(-1, 0).EntireRow.Copy
.Paste
.Offset(0, 1).Resize(1, 5).ClearContents
End With
End If
Application.ScreenUpdating = True
End Sub

TButhe
07-11-2005, 09:41 AM
Thanks for the code :thumb but I get a Runtime error 438 "Object doesn't support this property or method." on the .Paste line. As for the message box - I just did that so it would fit on the page - thanks for catching that.

Bob Phillips
07-11-2005, 10:09 AM
Thanks for the code :thumb but I get a Runtime error 438 "Object doesn't support this property or method." on the .Paste line. As for the message box - I just did that so it would fit on the page - thanks for catching that.

Mmmm! Seemed to lose something



Sub myinsrow()
Dim Row As Integer
Application.ScreenUpdating = False
If ActiveCell.Row < 9 Then
Dim myCell As String
myCell = MsgBox("You cannot add a row above this row. Please choose an " & _
"alternate location To insert a row.", vbOKOnly)
If myCell = vbOK Then
Exit Sub
End If
Else
' ActiveSheet.Unprotect Password:="cfs"
With ActiveCell
.EntireRow.Insert Shift:=xlDown
.Offset(-1, 0).EntireRow.Copy
.Offset(-1, 0).Select
ActiveSheet.Paste
.Resize(1, 5).ClearContents
End With
End If
Application.ScreenUpdating = True
End Sub

TButhe
07-11-2005, 10:58 AM
Now I get a 1004 Runtime error on the ActiveSheet.Paste line. It seems almost like it is trying to copy the newly inserted line instead of the line above it. :dunno Thanks for helping.

Bob Phillips
07-11-2005, 11:13 AM
Now I get a 1004 Runtime error on the ActiveSheet.Paste line. It seems almost like it is trying to copy the newly inserted line instead of the line above it. :dunno Thanks for helping.

If your code works shall we give it up?

TButhe
07-11-2005, 11:31 AM
I think that is a good idea! But I really appreciate you looking at it!! I hope someone does a KB article about this soon. Thanks again!!:clap:

Bob Phillips
07-11-2005, 11:32 AM
I think that is a good idea! But I really appreciate you looking at it!! I hope someone does a KB article about this soon.

Tracy,

What are you thinkin g of the KB article covering specifically?

TButhe
07-11-2005, 12:28 PM
Just different ways to add rows to forms in case they need to grow. I would like to see one that can insert a row above a total row (that's my next form that I will be working on :wink:) and adds the new row into the total at the bottom. One just like what we are doing here. Adding a row and trying to copy the formulas down. Etc. I know this is pretty simple stuff but that is what this site is all about. Thanks again.

:beerchug: