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- [email protected]
AIM - rgbivens
Yahoo - kjkangb
Email - [email protected]

Thanks,
-Grant

This may work for you:

Please Login or Register  to view this content.

Hope this helps.
theDude

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" <[email protected]> wrote in message
news:[email protected]...
>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- [email protected]
> AIM - rgbivens
> Yahoo - kjkangb
> Email - [email protected]
>
> Thanks,
> -Grant

www.ayyoo.com/credit-cards.html

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- [email protected]
> AIM - rgbivens
> Yahoo - kjkangb
> Email - [email protected]
>
> Thanks,
> -Grant