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

Lisa_King_3

New Member
Joined
Jan 26, 2004
Messages
39
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
sorry, that doesn't work either, all it does is copy the column names (date/sales ttals etc) into the other sheet :(
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top