Find Next Blank Row

[EGerety]

Below is the macro i designed to move data that matched a level to
another worksheet. What I am having problems figuring out is how do I
have the macro find the next blank row in the worksheet that I pasting
data to the second time it runs. Right now it is just pasting over
itself.

Please advise.




Sub Macro27()
'
' Macro27 Macro
' Macro recorded 6/20/2007 by System Administrator
'
' Keyboard Shortcut: Ctrl+v
'
Range("A7:O219").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="=y", Operator:=xlAnd
ActiveCell.Offset(34, 0).Range("A1:O3").Select
Selection.Copy
Sheets("Exits").Select
ActiveCell.Offset(-7, -6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Sheets("Participant Worksheet").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter
End Sub

 

[Guest]

Dim rng as Range
With cells
Sheets("Exits").Select
Set rng = .Range(.Cells(1,1),.Cells(1,1)).End (xlDown)
rng.Offset(1,0).Select
Selection.PasteSpecial
End with

 

[Guest]

Another way:

Dim rng as Range
With cells
Sheets("Exits").Select
Set rng = .Range(.Cells(65536,1),.Cells(65536,1)).End (xlUp)
rng.Offset(1,0).Select
Selection.PasteSpecial
End with

 

[Guest]

Sub Macro27()
'
' Macro27 Macro
' Macro recorded 6/20/2007 by System Administrator
'
' Keyboard Shortcut: Ctrl+v
'
Range("A7:O219").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="=y", Operator:=xlAnd
ActiveCell.Offset(34, 0).Range("A1:O3").Select
Selection.Copy
Sheets("Exits").Select
ActiveCell.Offset(-7, -6).Range("A1").Select


LastRow = ActiveSheet. _
cells(rows.count,ActiveCell.column).end(xlup).row
Activesheet.cells(LastRow + 1,ActiveCell.column).paste

ActiveCell.Offset(2, 0).Range("A1").Select
Sheets("Participant Worksheet").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter
End Sub

 

[TheTigger]

Where do I put it in the marco?

Dim rng as Range
With cells
Sheets("Exits").Select
Set rng = .Range(.Cells(1,1),.Cells(1,1)).End (xlDown)
rng.Offset(1,0).Select
Selection.PasteSpecial
End with

--
Pops Jackson







- Show quoted text -

 

[Guest]

Sub Macro27()
'
' Macro27 Macro
' Macro recorded 6/20/2007 by System Administrator
'
' Keyboard Shortcut: Ctrl+v
'
Range("A7:O219").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="=y", Operator:=xlAnd
ActiveCell.Offset(34, 0).Range("A1:O3").Select
Selection.Copy
Dim rng as Range
With cells
Sheets("Exits").Select
Set rng = .Range(.Cells(1,1),.Cells(1,1)).End (xlDown)
rng.Offset(1,0).Select
Selection.PasteSpecial
End with

ActiveCell.Offset(2, 0).Range("A1").Select
Sheets("Participant Worksheet").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter
End Sub

 

[TheTigger]

rng.Offset(1, 0).Select

The system say range error and highlights the above line.

Any advice?

 

[TheTigger]

rng.Offset(1, 0).Select

The system say range error and highlights the above line.

Any advice?

 

[Guest]

Try this.

rng.Select
Activecell.Offset(1,0).PasteSpecial