Results 1 to 9 of 9

Thread: Copy Row To Another Sheet Depending On Existence Of Specific Value In Specific Column

  1. #1
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12

    Copy Row To Another Sheet Depending On Existence Of Specific Value In Specific Column

    Hi i have attached a sample wb

    On it i have two sheets (but they will end up with 1 to 31) named 1 ton 31

    i can get any row tagged with the word copy in sheet1 to be coppied onto sheet 2, how can i then get any row tagged in sheet 2 to be coppied to 3 then 3 to 4 etc?
    it would be good if the copied row did not carry over the word copy onto the new sheet.

    Would i need to put a code in each sheet????


    Any help would be appriciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Peter, you have been guided regarding thread posting. Next time, please adhere to Thread Posting guidelines.

    Here's a modified version of your existing code. This can be used for all the sheets, except the last sheet in the workbook.
    Code:
     Sub SearchForString()
    
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
        On Error GoTo Err_Execute
        'Start search in row 6
        LSearchRow = 6
        'Start copying data to row 13 in Sheet2 (row counter variable)
        LCopyToRow = 13
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0
            'If value in column E = "Copy", copy entire row to Sheet2
            If Range("E" & CStr(LSearchRow)).Value = "Copy" Then
                With Sheets(ActiveSheet.Next.Name)
                    .Range(LCopyToRow & ":" & LCopyToRow).Value = _
                        Range(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Value
                    .Cells(LCopyToRow, "E").ClearContents
                End With
                'Move counter to next row
                LCopyToRow = LCopyToRow + 1
            End If
            LSearchRow = LSearchRow + 1
        Wend
        
        'Position on cell A3
        Range("A3").Select
        MsgBox "All Selected Rows Have Been Copied To The Next Sheet ."
        Exit Sub
    Err_Execute:
        MsgBox "An error occurred."
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi
    Thank you for the code,
    I have replaced my old code with yours in mod 1 and it works from sheet1 to sheet 2, but when i add another sheet it does not copy from 2 to 3 do i need to do something else for it to recognise future added sheets???

    Peter

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    No you don't. You just need to assign the same macro to each button on further sheets
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi

    Again forgive my stupidity, but i just copy a clone page and this copies everything, the time start macro works, but the copy wont, when i look on the sheet code
    the macro code is there???


    peter

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    The macro should not be in the sheet, but in the module. And no matter how many times you copy over a sheet, the macro will always remain that single one in the module. It's just that the button in each of the sheets will be assigned that macro.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi
    I still cant get this to work (dont know what i am doing wrong)
    if a copy a sheet after sheet one the copy button will work regardless of what the second sheet is named, but if i add another sheet when i press the button the pop up says all items coppied but nothing happens.
    When i copy the sheet the call searchforstring is in the coding in each sheet? there are no errors ?? have you any ideas

    Did you get it to work on my test file ???

    Peter

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    It worked on your test file. Like I said, the code should be in the code module. It should NOT be in the sheet code module. When you copy a sheet, the code DOESN'T get copied over and over again.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi

    I have been copying sheets by holding down the mousebutton and ctrl which copies every thing, is this not the way to do it ?

    Peter

Similar Threads

  1. Replies: 2
    Last Post: 06-24-2013, 07:40 PM
  2. Delte a specific column and does not delete the top row
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 06-13-2013, 02:00 PM
  3. Replies: 6
    Last Post: 06-06-2013, 03:17 AM
  4. Macro to copy data in specific Columns
    By Howardc in forum Excel Help
    Replies: 0
    Last Post: 04-19-2013, 10:42 AM
  5. Copy Row To A New Sheet If A Value Found In Adjacent Column
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-17-2012, 05:42 PM

Posting Permissions

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