Copy and paste to second Sheet (Conditional)

[Raph85]

Hi Excel experts,

I have a data that has a dept code on it on column A and I would like to copy only the data on dept code 1 to sheet 2.

Could you please kindly share your expertise on the VBA code for this method.

Thank you.

Raphael.

 

[Steve_R]

Hi Raphael

1) You do mean copy, not move (i.e. you don't want to delete and close rows up after copy)?
2) How do you plan to identify which department code to copy?
3) Is there only one row with the required department code (if so, I am thinking double click the code and it copies across)?
4) Only the department code and nothing else in the row?
5) Where do you want to put it (e.g. A2; column A, next blank row)?

It always helps if you able to post a sample (changed names for privacy).

To best way to post a sample is using an Excel html add-in, which allows those who answer to copy your values and paste into Excel (saves us time). There are a few choices listed under 'Posting Aids' in the following thread. I use MrExcel HTML Maker, which is easy to use.

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Raph85]

Hi Steve,

Thank you for the reply. I was looking for the way to post the data so it's easier to be understood. please find the answer to your questions as per below:

1) You do mean copy, not move (i.e. you don't want to delete and close rows up after copy)?
I would like to copy all the data under Dept code 1 to sheet 2, then I will clear all the data on Sheet 1. basically this is a
finance statistic data which I have to do it daily.

2) How do you plan to identify which department code to copy?
there is column A on the data which show the dept number.

3) Is there only one row with the required department code (if so, I am thinking double click the code and it copies across)?
there is multiple row, depending on the number of transactions happening on the previous day.

4) Only the department code and nothing else in the row?
I would like to copy the entire row under dept code 1.

5) Where do you want to put it (e.g. A2; column A, next blank row)?
I am thinking of putting the heading on row 1 in sheet 2 so that it could be copied on the next blank row. I think that would
be the easiest way.

Excel 2012
	A	B	C	D	E	F	G
1
2	Dept	Account	Curr/Stat	Account Desc	Amount	Account Type	Status
3
4	1	2100	RM	BAR	48	Revenue	Open
5	1	2100	AUD	BAR	-11478.62	Revenue	Open
6	1	2160	RM	CONS	7	Revenue	Open
7	1	2160	AUD	CONS	-1775.47	Revenue	Open
8	1	2300	RM	CORP	32	Revenue	Open
9	1	2300	AUD	CORP	-6132.34	Revenue	Open
10	1	2350	RM	LCL	15	Revenue	Open
11	1	2350	AUD	LCL	-3320.17	Revenue	Open
12	1	2500	RM	MK	47	Revenue	Open
13	1	2500	AUD	MK	-9653.68	Revenue	Open
14	1	2600	RM	DIS	23	Revenue	Open
15	1	2600	AUD	DIS	-3926.13	Revenue	Open
16	19	7010	AUD	MTG	-545.45	Revenue	Open
17	19	7510	AUD	AV	0	Revenue	Open
18	19	8990	AUD	FA	90.91	Expense	Open
19	25	7963	AUD	IM	-2.9	Revenue	Open
20	25	7980	AUD	SUN	0	Revenue	Open
21	31	7931	AUD	GV	-216.23	Revenue	Open
22	40	8130	AUD	LR	0	Revenue	Open
23	40	8140	COV	DR	13	Revenue	Open
24	40	8140	AUD	DR	-511.8	Revenue	Open
25	40	8990	AUD	FA	-0.24	Expense	Open
26	40	9110	AUD	BR	-179.54	Revenue	Open
27	99	96261	AUD	ST	-4037.26	Liability	Open
Net View

 

[FDibbins]

Does this have to be VBA or could a regular formula work?

 

[My Aswer Is This]

Try this script:

Sub FilterMini()
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="1", Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A" & Lastrow)
    End With
ActiveSheet.AutoFilterMode = False

End Sub

 

[My Aswer Is This]

If you want to delete those rows on sheet One after copying them to sheet 2 then use the below script.
This script will copy the rows to sheet two and then delete them on sheet one.

Sub FilterMini()
'Mod 2
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="1", Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A" & Lastrow)
        .SpecialCells(xlCellTypeVisible).Delete
    
    End With
ActiveSheet.AutoFilterMode = False

End Sub

 

[Raph85]

Hi,

I've tried the code but it's only copying the header on Row 2.

Thanks

 

[My Aswer Is This]

The script is looking for the value '1" in column "A" on Sheet(1).
And will copy that row to sheet 2.

 

[Steve_R]

Try removing the blanks between header and data

 

[My Aswer Is This]

The reason why my script did not work for you was because you never mentioned in your original post that your data began in row (4). Your original post was very lacking in detail.

It's very unusual for someone to put their header in row (2) and then start their data in row (4). You can always get better and quicker help if you give all these details in the beginning. Now 3 hrs. later you did post a copy of your sheet and now we can see that.

Try the below script:

Sub FilterMini()
'Mod 2
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Worksheets("Sheet1").Range("A4").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="1", Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A" & Lastrow)
    End With
ActiveSheet.AutoFilterMode = False

End Sub