Pasting Selected Rows To Another Sheet
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Pasting Selected Rows To Another Sheet

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I would like to check a worksheet column and paste a row if it has an f in the column i am checking. Can any of the guru's help me to do this in vb


    Thanks
    Roy

  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 Roy


    Here is some very quick code to narrow down your range the cells with an "f" anywhere in them. It assumes the data is in Column "A" and Column "B" is empty

    Sub DoIt()
    Dim rRange As Range
    Dim rCell As Range

    Range("A1", Range("A65536").End(xlUp)) _
    .Offset(0, 1).FormulaR1C1 = "=FIND(""f"",RC[-1])"

    Set rRange = Range("B1", Range("B65536").End(xlUp)).Offset(0, 1)
    rRange = rRange.Value

    Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    rRange.Clear

    For Each rCell In rRange
    'rCell.EntireRow 'DO WHAT YOU WANT.
    Next rCell


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That doesn't do what i want. I have several columns of data. I want to search column h and if column h contains "f" then i want to copy this to another sheet at the next empty row on the other sheet.

    Any help appreciated.
    Roy

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

    Default

    Hi Roy

    You have not even tried have you? Your post above is 7 minutes after my first reply!

    Make sure Column I is empty (insert a Column if it's not)

    Sub DoIt()
    Dim rRange As Range
    Dim rCell As Range

    Range("H1", Range("H65536").End(xlUp)) _
    .Offset(0, 1).FormulaR1C1 = "=FIND(""f"",RC[-1])"

    Set rRange = Range("I1", Range("I65536").End(xlUp)).Offset(0, 1)
    rRange = rRange.Value

    Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)


    For Each rCell In rRange
    rCell.EntireRow.Copy Destination:= _
    Sheets("Sheet1").Range("A65536").End(xlUp).Cells(2, 1)
    Next rCell
    rRange.Clear
    End If


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried this code but first it said no if block for end if. I removed the end if and i inserted a blank column i. The code then proceeded to copy all the rows into my new sheet??????.
    Any ideas what is going wrong

    Thanks m8
    Roy

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

    Default

    The original code will look for the letter "f" in the cell, so if the cell contained the word "Offle" it would assume a match. I now take it you want only cells that contain a single "f" and nothing else? If so try this.

    Sub DoIt()
    Dim rRange As Range
    Dim rCell As Range

    Range("H1", Range("H65536").End(xlUp)) _
    .Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]=""f"",1,NA())"

    Set rRange = Range("I1", Range("I65536").End(xlUp)).Offset(0, 1)
    rRange = rRange.Value

    Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)


    For Each rCell In rRange
    rCell.EntireRow.Copy Destination:= _
    Sheets("Sheet1").Range("A65536").End(xlUp).Cells(2, 1)
    Next rCell
    rRange.Clear

    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried the code again but i am getting an error at
    Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)

    If i delete this line then the code runs but again it copies the whole of the sheet, not just the rows containing f.

    Any idea's !!!!!
    Thanks
    Roy

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Come on guys i am really struggling with this problem, have tried all sorts, can anybody give me any suggestions / idea's.

    Cheerz
    Roy.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ^^^^bump^^^^

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Come on guys i am really struggling with this problem, have tried all sorts, can anybody give me any suggestions / idea's.

    Cheerz
    Roy.

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
  •  

 

 
DMCA.com