ExcelBanter - Find Next Blank Row

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find Next Blank Row (https://www.excelbanter.com/excel-programming/391714-find-next-blank-row.html)

Find Next Blank Row

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

Pops Jackson

Find Next Blank Row

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

" wrote:

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

Pops Jackson

Find Next Blank Row

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

--
Pops Jackson

" wrote:

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

joel

Find Next Blank Row

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

" wrote:

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

TheTigger

Find Next Blank Row

Where do I put it in the marco?

On Jun 20, 2:19 pm, Pops Jackson
wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -

Pops Jackson

Find Next Blank Row

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

--
Pops Jackson

"TheTigger" wrote:

Where do I put it in the marco?

On Jun 20, 2:19 pm, Pops Jackson
wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -

TheTigger

Find Next Blank Row

rng.Offset(1, 0).Select

The system say range error and highlights the above line.

Any advice?

Pops Jackson wrote:
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

--
Pops Jackson

"TheTigger" wrote:

Where do I put it in the marco?

On Jun 20, 2:19 pm, Pops Jackson
wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -

TheTigger

Find Next Blank Row

Pops Jackson

Find Next Blank Row

Try this.

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

--
Pops Jackson

"TheTigger" wrote:

rng.Offset(1, 0).Select

The system say range error and highlights the above line.

Any advice?

Pops Jackson wrote:
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

--
Pops Jackson

"TheTigger" wrote:

Where do I put it in the marco?

On Jun 20, 2:19 pm, Pops Jackson
wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -

All times are GMT +1. The time now is 03:30 PM.