VBA - copy specific rows from one to worksheet to another worksheet

[hrayani]

Hello Friends,

I am looking for a VBA code which could copy specific rows from one worksheet (Database) to another worksheet (Running order Status) within the same workbook

1) Copy from Database sheet to Running Order Status Sheet only rows where Column G (Status) showing In Process
2) Copy starting from Range("A4") till the last used row in Column A & paste only values in the Running Order Status sheet starting from Range("A4")

VBA Working.xlsm
	A	B	C	D	E	F	G	H
3	P.O #	REF	P.O.DATE	Customer	Suppliers	Values	STATUS	Actual Ship DATE
4	15556937 A	581	12-Nov-20	a	a	$ 330.00	Shipped	18-Mar-21
5	AH 27615 B1	582	10-Dec-20	b	b	$ 33,155.86	Shipped	30-Apr-21
6	357767	583	29-Dec-20	xx	xx	$ 32,325.12	Shipped	30-Jun-21
7	15590409 A	584	12-Dec-20	d	d	$ 9,055.20	Shipped	10-Feb-21
8	15624644 A + 15657650	585	11-Jan-21	a	a	$ 14,256.00	Shipped	15-Mar-21
9	586 A	586	30-Jan-21	b	b	$ 21,700.80	Shipped	10-Mar-21
10	387762	587	7-Apr-21	a	a	$ 86,355.36	In Process
11	362614	588	20-Apr-21	b	b	$ 854,027.12	In Process
12	379845	589	6-Apr-21	xx	xx	$ 339,365.12	In Process
13	362943	590	9-Apr-21	d	d	$ 316,199.36	In Process
14	15701543 A + 15703137	591	17-Mar-21	a	a	$ 9,801.00	Shipped	17-May-21
15	363359	592	20-Apr-21	b	b	$ 495,914.94	In Process
16	15740842 A	593	17-Apr-21	a	a	$ 11,616.00	Shipped	11-Jun-21
17	15713419 A	594	27-Mar-21	b	b	$ 455.04	Shipped	28-Jun-21
18	15758298 A	595	28-Apr-21	xx	xx	$ 810.54	Shipped	28-Jun-21
19	370343	596	2-Jun-21	d	d	$ 958,451.40	In Process
20	370336	597	12-May-21	a	a	$ 150,520.68	In Process
21	370089	598	21-May-21	b	b	$ 263,648.00	In Process
22	370090	599	21-May-21	a	a	$ 259,732.00	In Process
23	15774900 A	600	7-May-21	b	b	$ 11,510.40	Shipped	9-Jul-21
24	15774893 A	601	7-May-21	xx	xx	$ 476.37	Shipped	28-Jun-21
25	370287	602	9-Jun-21	d	d	$ 315,970.91	In Process
26	397033	603	17-Jun-21	a	a	$ 299,574.32	In Process
27	370413	604	7-Jun-21	b	b	$ 241,284.96	In Process
28	605	605	27-May-21	a	a	$ 25,432.00	Shipped	8-Jul-21
29	369805	606	8-Jun-21	b	b	$ 394,185.00	In Process
30	15823899 A	607	14-Jun-21	c	c	$ 11,536.80	In Process
Database

VBA Working.xlsm
	A	B	C	D	E	F	G	H
1
2
3	P.O #	REF	P.O.DATE	Customer	Suppliers	Values	STATUS	Actual Ship DATE
4	387762	587	7-Apr-21	a	a	$ 86,355.36	In Process
5	362614	588	20-Apr-21	b	b	$ 854,027.12	In Process
6	379845	589	6-Apr-21	xx	xx	$ 339,365.12	In Process
7	362943	590	9-Apr-21	d	d	$ 316,199.36	In Process
8	363359	592	20-Apr-21	b	b	$ 495,914.94	In Process
9	370343	596	2-Jun-21	d	d	$ 958,451.40	In Process
10	370336	597	12-May-21	a	a	$ 150,520.68	In Process
11	370089	598	21-May-21	b	b	$ 263,648.00	In Process
12	370090	599	21-May-21	a	a	$ 259,732.00	In Process
13	370287	602	9-Jun-21	d	d	$ 315,970.91	In Process
14	397033	603	17-Jun-21	a	a	$ 299,574.32	In Process
15	370413	604	7-Jun-21	b	b	$ 241,284.96	In Process
16	369805	606	8-Jun-21	b	b	$ 394,185.00	In Process
17	15823899 A	607	14-Jun-21	c	c	$ 11,536.80	In Process
Running Order Status

Any help would be appreciated

Regards,

Humayun

 

[Fluff]

How about

Sub hrayani()
   With Sheets("Database")
      .Range("A3:H3").AutoFilter 7, "In Process"
      .AutoFilter.Range.Offset(1).Copy
   End With
   Sheets("Running Order Status").Range("A4").PasteSpecial , xlPasteValues
   Application.CutCopyMode = False
End Sub

 

[hrayani]

Hi Fluff,

Thanks for the reply
I am getting error with this of the code highlighted - Run time error 1004... PAstespecial method of range class failed

Sheets("Running Order Status").Range("A4").PasteSpecial , xlPasteValues

One more thing as I requested in my initial post that instead of looking from A3:AH3 it should look from A3 till the last used row in Column A as the database will keep on growing

 

[Fluff]

Remove the comma from that line.

As long as you don't have any blank rows rows in the data the code will automatically filter the entire data.

 

[hrayani]

Thanks Worked for me...

Remove the comma from that line.

As long as you don't have any blank rows rows in the data the code will automatically filter the entire data.

Thanks Fluff

Now working just Perfect

 

[Fluff]

You're welcome & thanks for the feedback.

 

[hrayani]

You're welcome & thanks for the feedback.

Hi,
sorry for this but i noticed it just now

can we move that auto filter from the database sheet when the copy paste is done
I want the database sheet to be as it was

EDIT:

just added this line & it worked

.AutoFilterMode = False

 

[Fluff]

Yup, like

Sub hrayani()
   With Sheets("Database")
      .Range("A3:H3").AutoFilter 7, "In Process"
      .AutoFilter.Range.Offset(1).Copy
      .AutoFilterMode = False
   End With
   Sheets("Running Order Status").Range("A4").PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

 

[hrayani]

Thanks again

 

[Fluff]

My pleasure.