Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Move row to another sheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I need some help on below:
    If the cell entry is "yes", then the whole row should be moved (cut) to an empty row in another sheet (e.g. sheet2).
    If possible, using built-in formulae

    Many thanks

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi A.B

    Not with a function no! Would you like some VBA code?



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub cuttosheet()
    Dim ir As Integer
    Dim i As Integer
    ir = [a1].CurrentRegion.Rows
    For i = 1 To ir
    sheet1.select
    If Range("a:" & i).Value = "yes" Then
    Range("a:" & i).EntireRow.Cut
    Sheet2.Select
    Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.PasteSpecial xlPasteAll
    End If
    Next i
    End Sub

    This code should work..
    I have not checked the code..if you find difficulty then write back to me.

    ni****h desai
    http://www.pexcel.com

    [ This Message was edited by: nisht on 2002-03-27 02:21 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you all
    It's OK with VB code (since no other way), but can you make it as a user-defined funtion?

    Nisht,
    The code returns run-time error 13 "type mismatch" line 4

    Thanks again

  5. #5

    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 09:07, A.B. wrote:
    Thank you all
    It's OK with VB code (since no other way), but can you make it as a user-defined funtion?

    Nisht,

    The code returns run-time error 13 "type mismatch" line 4

    Thanks again
    Functions cannot change the Excel environment, they can only return results.

    Try this macro :-

    Sub cuttosheet()
    Dim sRng As Range, cell As Range
    Dim dRng As Range
    Set sRng = Sheets("Sheet1").Range([A1], [A65536].End(xlUp))
    For Each cell In sRng
    If cell.Value = "yes" Then
    Set dRng = Sheets("Sheet2").[A65536].End(xlUp)(2, 1)
    cell.EntireRow.Cut dRng
    End If
    Next
    End Sub

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Excellent
    Two more things:
    1) If the column that contains "yes/no" is in e.g. D
    2) Delete the entire "copied" rows in sheet1

    what would be the changes?

    cheers

  7. #7
    New Member
    Join Date
    Dec 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move row to another sheet

    I have a similar request... Can this be modified so that instead of cutting the row and moving to another sheet, the row is copied to another sheet? Also, instead of "yes" being the criteria I want to copy the row if the value in column K is >0.

    Thanks for any help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •