1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by KathrynJ, Mar 20, 2017.

  1. KathrynJ

    KathrynJ New Member

    Messages:
    6
    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?
  2. Monty

    Monty Well-Known Member

    Messages:
    631
    KathrynJ

    Welcome to forum.

    Sample file will provide you targeted solution.
    Arpanakumar and jamesexcel1970 like this.
  3. Monty

    Monty Well-Known Member

    Messages:
    631
    Try this!

    Code (vb):
    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
     
    Arpanakumar and jamesexcel1970 like this.
  4. Monty

    Monty Well-Known Member

    Messages:
    631
    Here is the file ...change sheet names as per your requirment!

    Attached Files:

  5. KathrynJ

    KathrynJ New Member

    Messages:
    6
    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
  6. Monty

    Monty Well-Known Member

    Messages:
    631
    Hello

    From my code change copy to cut....You are done.
    Arpanakumar likes this.
  7. KathrynJ

    KathrynJ New Member

    Messages:
    6
    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?
  8. KathrynJ

    KathrynJ New Member

    Messages:
    6

    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

Share This Page