Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need to find the first empty row in a sheet to paste data to
I have a form on sheet 1 (New User) that when you press the 'Add' button it
needs to find the first empty row in sheet 2 (Data) and paste the data from the form there. I have gotten it to where the data gets copied, but only on a row that I select when I'm recording a macro. Here is what my code looks like: ------------------------------------------------- Sub NewEntry() ' ' NewEntry Macro ' Macro recorded 4/6/2005 by Grant Bivens. ' ' Keyboard Shortcut: Ctrl+a ' Sheets("Data").Select ActiveSheet.Unprotect Sheets("New").Select Range("A4:G4").Select Selection.Copy Sheets("Data").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("A4,C4,E4,G4").Select Range("G4").Activate With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("B3").Select Selection.AutoFill Destination:=Range("B3:B4"), Type:=xlFillDefault Range("B3:B4").Select ActiveSheet.Protect End Sub ------------------------------------------------- As you can see the data is just copied to row 4, over writing it each time. Also, I need it to color every other cell for easy of viewing. If anybody would help me with this project I would be willing to slip some funds across the table if you know what I mean. This is really important for me to get done. Its for a list of under 13 users at a forum I moderate. Its a large list and our current system is virtually impossible to wade through. You can reach me many different ways: MSN- AIM - rgbivens Yahoo - kjkangb Email - Thanks, -Grant |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need to find the first empty row in a sheet to paste data to
Hi Grant,
Seeing as A4 is the first cell in your table, you could just use: Range.("A4").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select This will select cell A4, go to the last non-blank cell in the list, and then go down 1 cell further. Hope this was what you were looking for. Warm regards Sunil Jayakumar "rgbivens" wrote in message ... I have a form on sheet 1 (New User) that when you press the 'Add' button it needs to find the first empty row in sheet 2 (Data) and paste the data from the form there. I have gotten it to where the data gets copied, but only on a row that I select when I'm recording a macro. Here is what my code looks like: ------------------------------------------------- Sub NewEntry() ' ' NewEntry Macro ' Macro recorded 4/6/2005 by Grant Bivens. ' ' Keyboard Shortcut: Ctrl+a ' Sheets("Data").Select ActiveSheet.Unprotect Sheets("New").Select Range("A4:G4").Select Selection.Copy Sheets("Data").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("A4,C4,E4,G4").Select Range("G4").Activate With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("B3").Select Selection.AutoFill Destination:=Range("B3:B4"), Type:=xlFillDefault Range("B3:B4").Select ActiveSheet.Protect End Sub ------------------------------------------------- As you can see the data is just copied to row 4, over writing it each time. Also, I need it to color every other cell for easy of viewing. If anybody would help me with this project I would be willing to slip some funds across the table if you know what I mean. This is really important for me to get done. Its for a list of under 13 users at a forum I moderate. Its a large list and our current system is virtually impossible to wade through. You can reach me many different ways: MSN- AIM - rgbivens Yahoo - kjkangb Email - Thanks, -Grant www.ayyoo.com/credit-cards.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need to find the first empty row in a sheet to paste data to
sheets("Data").range("A65535").end(xlUp).offset(1, 0).select
This gets you the first empty row based on column A... HTH "rgbivens" wrote: I have a form on sheet 1 (New User) that when you press the 'Add' button it needs to find the first empty row in sheet 2 (Data) and paste the data from the form there. I have gotten it to where the data gets copied, but only on a row that I select when I'm recording a macro. Here is what my code looks like: ------------------------------------------------- Sub NewEntry() ' ' NewEntry Macro ' Macro recorded 4/6/2005 by Grant Bivens. ' ' Keyboard Shortcut: Ctrl+a ' Sheets("Data").Select ActiveSheet.Unprotect Sheets("New").Select Range("A4:G4").Select Selection.Copy Sheets("Data").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("A4,C4,E4,G4").Select Range("G4").Activate With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("B3").Select Selection.AutoFill Destination:=Range("B3:B4"), Type:=xlFillDefault Range("B3:B4").Select ActiveSheet.Protect End Sub ------------------------------------------------- As you can see the data is just copied to row 4, over writing it each time. Also, I need it to color every other cell for easy of viewing. If anybody would help me with this project I would be willing to slip some funds across the table if you know what I mean. This is really important for me to get done. Its for a list of under 13 users at a forum I moderate. Its a large list and our current system is virtually impossible to wade through. You can reach me many different ways: MSN- AIM - rgbivens Yahoo - kjkangb Email - Thanks, -Grant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Special Paste to Next empty row if new data and mark copied Macro | Excel Discussion (Misc queries) | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
Find range in one sheet, then paste in the other (one by one) | Excel Programming | |||
macro to copy paste non empty data | Excel Programming | |||
Find Empty Column and paste cell values | Excel Programming |