PDA

View Full Version : Copy filtered records and paste another worksheet in Excel VBA



Kaniguan1969
09-02-2014, 01:55 AM
Hi,

I am working in excel vba to copy a filtered records and paste to another worksheet in the last empty rows of column D.
another requirements, i have to get the model name string from the reference. Please see a sample data.
Anybody help is very much appreciated. thank you.



Transaction#--- Reference -- Qty---- ETA-- Model
-----------------------------------------------------------------
8840863146-- FALV3000430iPH5S32SIL --10 --31--Aug-- #N/A
8840863146-- FALV3000430iPH5S64SIL --10 --31--Aug-- #N/A
8840863146-- SALV3003110REVERE3 ----10 --31--Aug-- #N/A
8840863146-- FALV3000460iPH5S64GLD--10 --31--Aug-- #N/A
8840863146-- SALV3003110VS985 ------20---31--Aug-- #N/A
8840863146-- PP3000440iPH5C16BLU ---10 ---31--Aug-- #N/A

Desired Result:
Reference Model Model
----------------------------------------------------
FALV3000430iPH5S32SIL iPH5S32SIL iPH5S32SIL
FALV3000430iPH5S64SIL iPH5S64SIL iPH5S64SIL
SALV3003110REVERE3 REVERE3 REVERE3
FALV3000460iPH5S64GLD iPH5S64GLD iPH5S64GLD
SALV3003110VS985 VS985 VS985
PP3000440iPH5C16BLU iPH5C16BLU iPH5C16BLU

Vba macro code:

With ws
Set rng = ws.Range("A2:A" & ws.Cells(Rows.count, 1).End(xlUp).Row)
rng.AutoFilter Field:=5, Criteria1:="#N/A"
lrow = .Range("A" & Rows.count).End(xlUp).Row

End With