Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Find Next Blank Row
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 -


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default 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 -



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 -




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 -




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default 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 -





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... [email protected][_2_] Excel Programming 2 June 7th 07 09:27 PM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Find next blank row Vlad999[_26_] Excel Programming 3 July 6th 06 03:36 AM
VB: Find blank row then add another blank row Tbal[_3_] Excel Programming 1 July 18th 05 08:33 PM
VB: How do I find next blank row then add another blank row TB Excel Programming 1 July 18th 05 08:33 PM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement