Totally Stumped
I have a contact list I made for my organization with sort and search functions and some other features. To make it so that users of the file don't mess up the formulas I have most of the operations hidden. To make it so I don't have to have more rows populated with formulas than there are current entries, I have made it so that to create space for a new, blank record you must press a button. The file hadn't been used for awhile, but someone in the office loaded it up yesterday an tried to add a new row. She noticed that it just copied the old row and put it there and she would have to go back and edit it.
Upon further inspection I found out that the macro I had written was offesetting everything by one column for no reason, so the operating area that was supposed to be columns B:AG is now C:AH. All it does now is create a new row of C:AH Additionally, because the way I am making everything operate is Range("B65536").End(xlUp) and there is now no B column in the last row, this creates more problems. I know I could do this by defining a range instead of repeating the xlUp, but I thought that this was easier when I wrote it. I turn off Events in the middle of this so I know it's not another macro doing something. Any Ideas? I'm totally stumped.
Here's the Code:
Sub NewRowPopulate()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("B65536").End(xlUp).Columns("B:AG").Copy 'Selects C:AH and copies
Range("B65536").End(xlUp).Columns("B:AG").Offset(1, 0).PasteSpecial (xlPasteAll)'Pastes in the correct row but C:AH pasted in C:AH and there is no value in B
Range("B65536").End(xlUp).Columns("C:C").ClearContents'Because there is now no data in column B of the last row all these operations are performed on the 2nd to last, or originator row. Because they are offset a column, the things that are cleared are my cells with matching formulas and not the places for user data that are supposed to be. In practice its an offset of (-1,1)
Range("B65536").End(xlUp).Columns("C:C").Locked = False
Range("B65536").End(xlUp).Columns("C:AG").Interior.ColorIndex = 2
Range("B65536").End(xlUp).Columns("F:F").ClearContents
Range("B65536").End(xlUp).Columns("F:F").Locked = False
Range("B65536").End(xlUp).Columns("I:I").ClearContents
Range("B65536").End(xlUp).Columns("I:I").Locked = False
Range("B65536").End(xlUp).Columns("L:L").ClearContents
Range("B65536").End(xlUp).Columns("L:L").Locked = False
Range("B65536").End(xlUp).Columns("O:O").ClearContents
Range("B65536").End(xlUp).Columns("O:O").Locked = False
Range("B65536").End(xlUp).Columns("Q:T").ClearContents
Range("B65536").End(xlUp).Columns("Q:T").Locked = False
Range("B65536").End(xlUp).Columns("AA:AG").ClearContents
Range("B65536").End(xlUp).Columns("AA:AG").Locked = False
Application.EnableEvents = True'From here on it seems fine
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
I have a contact list I made for my organization with sort and search functions and some other features. To make it so that users of the file don't mess up the formulas I have most of the operations hidden. To make it so I don't have to have more rows populated with formulas than there are current entries, I have made it so that to create space for a new, blank record you must press a button. The file hadn't been used for awhile, but someone in the office loaded it up yesterday an tried to add a new row. She noticed that it just copied the old row and put it there and she would have to go back and edit it.
Upon further inspection I found out that the macro I had written was offesetting everything by one column for no reason, so the operating area that was supposed to be columns B:AG is now C:AH. All it does now is create a new row of C:AH Additionally, because the way I am making everything operate is Range("B65536").End(xlUp) and there is now no B column in the last row, this creates more problems. I know I could do this by defining a range instead of repeating the xlUp, but I thought that this was easier when I wrote it. I turn off Events in the middle of this so I know it's not another macro doing something. Any Ideas? I'm totally stumped.
Here's the Code:
Sub NewRowPopulate()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("B65536").End(xlUp).Columns("B:AG").Copy 'Selects C:AH and copies
Range("B65536").End(xlUp).Columns("B:AG").Offset(1, 0).PasteSpecial (xlPasteAll)'Pastes in the correct row but C:AH pasted in C:AH and there is no value in B
Range("B65536").End(xlUp).Columns("C:C").ClearContents'Because there is now no data in column B of the last row all these operations are performed on the 2nd to last, or originator row. Because they are offset a column, the things that are cleared are my cells with matching formulas and not the places for user data that are supposed to be. In practice its an offset of (-1,1)
Range("B65536").End(xlUp).Columns("C:C").Locked = False
Range("B65536").End(xlUp).Columns("C:AG").Interior.ColorIndex = 2
Range("B65536").End(xlUp).Columns("F:F").ClearContents
Range("B65536").End(xlUp).Columns("F:F").Locked = False
Range("B65536").End(xlUp).Columns("I:I").ClearContents
Range("B65536").End(xlUp).Columns("I:I").Locked = False
Range("B65536").End(xlUp).Columns("L:L").ClearContents
Range("B65536").End(xlUp).Columns("L:L").Locked = False
Range("B65536").End(xlUp).Columns("O:O").ClearContents
Range("B65536").End(xlUp).Columns("O:O").Locked = False
Range("B65536").End(xlUp).Columns("Q:T").ClearContents
Range("B65536").End(xlUp).Columns("Q:T").Locked = False
Range("B65536").End(xlUp).Columns("AA:AG").ClearContents
Range("B65536").End(xlUp).Columns("AA:AG").Locked = False
Application.EnableEvents = True'From here on it seems fine
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
