Copy all out items from sheet1 to sheet2.

[bentxoxo]

Hi All,

I am completely new to this site and <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.

Could someone help me with how I can create the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> in excel to do the followings?

From sheet1 find "out of stock" / "discontinue" items and copy item code,item name to sheet2, do this until all out/dis item are listed in sheet2.

We have more that 4000 items and it's very difficult to remember all of the out of stock / discontinue items. Having this VBA will save lots of time spend on manual editing.

I would be really grateful for your supports!!!

 

[Domski]

Have you tried using autofilter?

Dom

 

[bentxoxo]

Hi Dom,
Thanks for your reply. I tried filter but it doesn't work as I have multiple items in same row like A,B,C have different product range and D,E,F have different product range as you can see below "sheet1". I would to great if I have VBA which can copy all the out/dis products from whole sheet1 to sheet2.

Sheet1:

ITEM

ITEM CODE

QTY

TESTER

QTY

ITEM

ITEM CODE

QTY

TESTER

QTY

ITEM

ITEM CODE

QTY

TESTER

QTY

ITEM

ITEM CODE

QTY

TESTER

QTY

Shocking pink

1111

111T

Eyeliner Black

2222

Face Wash

5555

fuchia pink

1112

112T

OUT

Mascara Black

2223

223T

Cleansing Milk

3333

Face Scrub

5556

OUT

556T

red velvet

1113

OUT

113T

Blackest/Black

2224

224T

Dual Action Cleanser

3334

Moisturiser

5557

557T

DIS

copper red

1114

114T

Volume Black

2225

225T

DIS

Toner

3335

Moisturiser

5558

DIS

558T

dis

wild pink

1115

OUT

115T

Vol Brown/Black

2226

226T

DIS

Daily Moist

3336

OUT

336T

Moisturiser

5559

out

559T

DIS

old rose

1116

116T

Black Rose

2227

DIS

227T

OUT

Moisture Treat

3337

OUT

337T

out

Eye Serum

5560

bloom pink

1117

117T

DIS

Mascara Black

2228

228T

Night Cream

3338

OUT

Deodorant

5561

hot pink

1118

OUT

118T

EYE-3

Day Cream

3339

339T

Body Wash

5562

dis

pinky orange

1119

119T

Eye Cream

3340

340T

dis

Shave Gel

5563

OUT

563T

DIS

<tbody>
</tbody>

Sheet2:

OUT OF STOCK
Item Code	Name	Expected Delivery
1113	red velvet	20-Jan-16
1118	hot pink	Jan End

<tbody>
</tbody>

Thanks

 

[bentxoxo]

Here's like to excel file.

https://www.dropbox.com/s/puju257mowmc7ev/Test%20Stock.xlsx?dl=0&s=sl

Thanks

 

[Domski]

I can't access that but the code would be something like this:

Sub Macro1()        
    Dim rngLoopRange As Range
    
    For Each rngLoopRange In Sheets("Sheet1").Range("C2:C10,H2:H10,L2:L10,S2:S10")
        If UCase(rngLoopRange) = "OUT" Then
            With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Value = rngLoopRange.Offset(0, -1)
                .Offset(0, 1) = rngLoopRange.Offset(0, -2)
            End With
        End If
    Next rngLoopRange
    
End Sub

You'd find it much easier to do analysis on your data if it was all kept together rather than split up across the sheet.

Dom

 

[bentxoxo]

Hi Dom,

That worked perfectly like a charm with little tweep n tweak. I can't thank you enough on how much you saved my time and manual copy / paste.