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

[Lisa_King_3]

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

 

[Lisa_King_3]

sorry, i know why it wasn't working, it was column 6 i was supposed to be searching, not 5. 5 is a blank column. It is working now, but the only thing is...it also copies the column titles? is there anyway of getting rid of it do you know?

Thanks a lot by the way

 

[north19701]

Sub Macro7()
Set Source = Sheets("membership sales").UsedRange
With Source
    .AutoFilter Field:=6, Criteria1:="2"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("weekly").Range("a1")
End With
Sheets("membership sales").ShowAllData
End Sub

I went back to the original code i posted and adjusted it to the actual sheets and to filter column 6. it will copy everything except for the header on the first sheet and post it to A1 on the second sheet. Hopefully, this will work for you.

 

[Lisa_King_3]

that works great! thanks a lot, you've really helped me out

 

[north19701]

your welcome

 

[Lisa_King_3]

:wink:

 

[R1chard]

hey, sorry to intude but i was looking for a similar formula. I'm using a test setup at the moment so i have used the first example with sheet1 ans sheet2. I t doesn't seem to work though! where do i paste this VB? on what sheet or what? im only a novice!

 

[R1chard]

sorry it does work apart from on sheet one it turns the headings into drop down lists. Why is that?

Also how do you get a macro to auto run when you click on the relevant sheet?

 

[Lisa_King_3]

Hi, this is the overall formula I used in the end. This one has an input box that asks for the value you want to search the colum for. It also removes the autofilter from the column after it has copied the data from sheet1 to sheet2.


Sub Macro1()

Dim findthis As String

findthis = InputBox("Enter Value To Search For", "Enter")

Sheets("Sheet1").Select
Columns("F:F").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=findthis
Sheets("Sheet1").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A9").PasteSpecial
Sheets("Sheet1").ShowAllData
Range("1:1").Select
Selection.AutoFilter
Sheets("Sheet2").Select

End Sub


Hope this helps, Lisa x