Page 1 of 2 12 LastLast
Results 1 to 10 of 18

vb code to search a column for a value and copy the row

This is a discussion on vb code to search a column for a value and copy the row within the Excel Questions forums, part of the Question Forums category; hi people. i'm having some trouble with VB . I have 5 columns in sheet 1: Date/Age/Sale Type/Total Sales/Week Number ...

  1. #1
    New Member
    Join Date
    Jan 2004
    Posts
    39

    Default vb code to search a column for a value and copy the row

    hi people. i'm having some trouble with VB.

    I have 5 columns in sheet 1: Date/Age/Sale Type/Total Sales/Week Number

    I need a macro code that will search the 5th column: week number for a value e.g. 2. Once it has found that value, it copies the data from the other 4 cells in the row and transfers it to A1 in sheet 2. The column will have more than one entry for each week, so it needs to keep searching the column until all value 2's are found and copy their ascociated data.

    I hope you understand me, and can help xxx

  2. #2
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: vb code to search a column for a value and copy the row

    Code:
    Sub Macro7()
    Set Source = Sheets("Sheet1").UsedRange
    With Source
        .AutoFilter Field:=5, Criteria1:="2"
        .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("a1")
    End With
    Sheets("Sheet1").ShowAllData
    End Sub

    Does this help?

  3. #3
    New Member
    Join Date
    Jan 2004
    Posts
    39

    Default Re: vb code to search a column for a value and copy the row

    er... no it doesn't, that just puts an autofilter in the columns...?

  4. #4
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: vb code to search a column for a value and copy the row

    it filters out instances of 2 in the 5th column and copies those rows that match onto sheet 2. I tested it and it works fine for me? where does it not work for you?

  5. #5
    New Member
    Join Date
    Jan 2004
    Posts
    39

    Default Re: vb code to search a column for a value and copy the row

    it doesn't copy them into the other sheet

  6. #6
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: vb code to search a column for a value and copy the row

    and your sheets are named "Sheet1" and "Sheet2", correct?

  7. #7
    New Member
    Join Date
    Jan 2004
    Posts
    39

    Default Re: vb code to search a column for a value and copy the row

    no, sheet1 is membership sales and sheet 2 is weekly. But that shouldn't matter because i changed the names in vb to the right ones

  8. #8
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: vb code to search a column for a value and copy the row

    Code:
    Sub test3()
    Sheets("membership sales").UsedRange.AutoFilter Field:=5, Criteria1:="2"
    Sheets("membership sales").UsedRange.SpecialCells(xlCellTypeVisible).Copy
    Sheets("weekly").Range("A1").PasteSpecial
    Sheets("membership sales").ShowAllData
    End Sub
    I have no idea why the first one wouldn't work, you can try this instead and see if you get better results.

  9. #9
    New Member
    Join Date
    Jan 2004
    Posts
    39

    Default Re: vb code to search a column for a value and copy the row

    sorry, that doesn't work either, all it does is copy the column names (date/sales ttals etc) into the other sheet

  10. #10
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: vb code to search a column for a value and copy the row

    are there values of 2 in column 5?

Page 1 of 2 12 LastLast

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