Data filtering from one worksheet to another

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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




1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.