Consulting

Results 1 to 5 of 5

Thread: Solved: copy rows on criteria

  1. #1
    VBAX Regular
    Joined
    Jan 2011
    Posts
    62
    Location

    Unhappy Solved: copy rows on criteria

    HI All

    I have two excel sheets one is adjustable other is fixed. I need that the adjustable sheet follow the sequence of fixed sheet and based on Fixed sheet column A. Give the result in result tab, if result tab is not created. It auto create the result sheet,in adjustable sheet copy the whole row based on the sequence Column A of fixed sheet.

    * My first sheet is name adjustable second is fixed.
    * i need that find string in adjustable sheet suppose "HI" then look in (fixed sheet) where the name "HI" exist if "HI" is place at column A row 12 then copy the entire row of adjustable sheet in new sheet which is result sheet at row 12?

    I need to to with all strings in the adjustable sheet?

    The sample file is attached with.

    thanks
    hammeed
    Attached Files Attached Files

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    hi.

    try this.

    [VBA]Sub find_copy_rows()

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim adjustRng As Range, findthisCell As Range
    Dim fixedRng As Range, foundCell As Range
    Dim findString As String, foundRow As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    On Error Resume Next
    Set ws1 = Worksheets("adjustable")
    Set ws2 = Worksheets("fixed")
    Set ws3 = Worksheets("result")
    If ws3 Is Nothing Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = "result"
    End If
    On Error GoTo 0

    Set adjustRng = ws1.Range("A1:A" & ws1.Cells(Rows.Count, 1).End(xlUp).Row)
    Set fixedRng = ws2.Range("A1:A" & ws2.Cells(Rows.Count, 1).End(xlUp).Row)

    ws3.UsedRange.Clear

    For Each findthisCell In adjustRng
    findString = findthisCell.Value
    Set foundCell = fixedRng.Find(What:=findString, after:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
    If Not foundCell Is Nothing Then
    foundRow = foundCell.Row
    ws1.Rows(findthisCell.Row).EntireRow.Copy Destination:=ws3.Rows(foundRow)
    End If
    Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub[/VBA]
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Regular
    Joined
    Jan 2011
    Posts
    62
    Location
    HI mancubus,

    Thank you so much for your kind help, this code worked like a charm.
    I just comment out end if both it worked for me. Thank you so much for your kind support.

    God Bless you and this forum Moderators.

    Thanks
    farrukh

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    youre wellcome farrukh.
    glad it helped.

    pls mark the thread as solved from thread tools.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    VBAX Regular
    Joined
    Jan 2011
    Posts
    62
    Location

    Thumbs up

    hi

Posting Permissions

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