I need to find the first empty row in a sheet to paste data to

[Guest]

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- (e-mail address removed)
AIM - rgbivens
Yahoo - kjkangb
Email - (e-mail address removed)

Thanks,
-Grant

 

[Sunil Jayakumar]

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

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- (e-mail address removed)
AIM - rgbivens
Yahoo - kjkangb
Email - (e-mail address removed)

Thanks,
-Grant

www.ayyoo.com/credit-cards.html

 

[Guest]

sheets("Data").range("A65535").end(xlUp).offset(1,0).select

This gets you the first empty row based on column A...

HTH