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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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?
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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?
 

Lisa_King_3

New Member
Joined
Jan 26, 2004
Messages
39

ADVERTISEMENT

it doesn't copy them into the other sheet
 

Lisa_King_3

New Member
Joined
Jan 26, 2004
Messages
39

ADVERTISEMENT

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
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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.
 

Lisa_King_3

New Member
Joined
Jan 26, 2004
Messages
39
sorry, that doesn't work either, all it does is copy the column names (date/sales ttals etc) into the other sheet :(
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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
Top