Data filtering from one worksheet to anotherClosed

Question

Hello,

I have a excel worksheet with inventory data in it. 
Column A is Item Number
Column B is Type of Coating
Column C is Stock Level to maintain
Column D is Stock on Hand
Column E is Qty. to Order
Column F is Ordered

There are 220 rows in the worksheet (and growing) 

I have the basics covered, =IF(D7<C7,C7-D7,"").

What I'm wondering is beyound the basics I believe.

Here's my question. Is it possible when Column F has a X in it, to copy that row of information using only Column A, B, E into an existing worksheet, in the same workbook, named Order Form that is already formatted with a company logo and a place for a P.O.#. This then can be printed out to give to the sales person rather then copy/paste manually or taking forever giving a verbal or written order, which sometimes leads to mistakes.

From browsing the internet it seems possible, but all the examples A)involve using VBA, which i can follow, but have problems modifing for this purpose. 
B)copy the entire row if column is equal to "X" when I only need the three columns of information for each row.

Thanks in advance.
JP 




Configuration: Windows Vista / Internet Explorer 8.0

venkat1926 · Answer

quote 
 named Order Form that is already formatted with a company logo and a place for a P.O.#unquote 
this is not clear.  I assumed that in the second sheet the columns A and B are to be left blank for entering company logp amd PO# 

the main data in sheet 1 (sample data given below) 
no blank rows. 

item number type of coating stock level stock onhand qty to order ordered 
1 a 20 2 18 18 
2 s 20 4 16 x 
3 d 20 20 0 0 
4 f 20 5 15 x 
5 g 20 8 12 12 

CONVERT FORMULAS INTO VALUES

now try this macro on this sample sheet. result is on sheet 2 

the macro is  

Sub test()
Dim r As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion
'MsgBox r.Address
r.AutoFilter field:=6, Criteria1:="x"
r.Cells.SpecialCells(xlCellTypeVisible).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).PasteSpecial
.Range("c1:d1").EntireColumn.Delete
.Range("A1:B1").EntireColumn.Insert
End With
ActiveSheet.AutoFilterMode = False
End Sub


if necessary modify the macro.

Data filtering from one worksheet to another

1 response

Similar discussions

Subscribe To Our Newsletter!