• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cut and Paste Rows using variable row count

KathrynJ

New Member
I want to find all the cells in column A that begin with "OM", and then cut the entire rows and move (paste) them into a second worksheet. But each month the row count will change - so I can't use a simple range of rows (i.e., rows 100-200). They will vary from month to month. Is there a macro that will work?
 
Try this!

Code:
Sub MONTY()
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:1").Select: Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$" & lrow).AutoFilter Field:=1, Criteria1:="OM"
Range("A2:I" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End Sub
 
Try this!

Code:
Sub MONTY()
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:1").Select: Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$" & lrow).AutoFilter Field:=1, Criteria1:="OM"
Range("A2:I" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End Sub

This works up to a point. I actually want to remove/cut the rows from the first sheet, not simply copy them. But I can't seem to get that to work.

Here's my code:

Dim lrow As Long
lrow = Range("D" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Select
Rows("6:6").Select: Selection.AutoFilter
ActiveSheet.Range("$D$6:$D$" & lrow).AutoFilter Field:=1, Criteria1:="OM*"
Range("D7:S" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cut
Worksheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End Sub
 
Hello

From my code change copy to cut....You are done.

When I change copy to cut, I get an error on the last row: "PasteSpecial method of Range class failed".

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

Any other suggestions?
 
When I change copy to cut, I get an error on the last row: "PasteSpecial method of Range class failed".

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

Any other suggestions?


Here's a way I made it work by adding code to delete the rows from sheet1.

Dim lrow As Long
lrow = Range("D" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Select
Rows("6:6").Select: Selection.AutoFilter
ActiveSheet.Range("$D$6:$D$" & lrow).AutoFilter Field:=1, Criteria1:="OM*"
Range("D7:S" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial


Sheets("Sheet1").Select
Rows("6:6").Select: Selection.AutoFilter
ActiveSheet.Range("$D$6:$D$" & lrow).AutoFilter Field:=1, Criteria1:="OM*"
Range("D7:S" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete
 
Back
Top