VBA code to copy data from sheet to another

[mazher]

Dear Forum Members,
Please can someone help me with the following;

I Sheet2 I have invoices data (containing formulas) starting from
B2:K7000

Every month it keeps on increasing downward.


B1:K1 are column headings

Column K is the payment date

Currently, I copy the data of Sheet2 manually in Sheet7 and then delete the rows that have been paid in column K, to get an upto date list of the pending invoices.

I am pasting the data using paste special to remove the formulas.

Please can someone help me to automate this task?

Thanks in advance.

 

[mazher]

Thank you very much @Alex Blakenburg

I started working,

I didn't realise that missing column heading can be a problem on my side.


I will be extremely thankful to you if you can please add these two conditions to the code for filtering the data so that the code does not copy the data from the "Data Inv" Sheet where the
Values in Column I (N Total are zero or empty)
Cells in Column B (Invoice No) are empty

Thanks in advance.

 

[Alex Blakenburg]

Give this a try:

Sub CopyPasteUnpaid()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
       
    Set srcSht = Worksheets("Data Inv")
    Set destSht = Worksheets("Pending")
    srcRowFirst = 1
        
    With srcSht
        srcRowLast = srcSht.Cells(Rows.Count, "A").End(xlUp).Row
        srcColLast = srcSht.Cells(srcRowFirst, Columns.Count).End(xlToLeft).Column
        Set rngSrc = .Range(.Cells(srcRowFirst, "A"), .Cells(srcRowLast, srcColLast))
        ' Temporary range for criteria
        .Columns(srcColLast + 2).Resize(, 4).EntireColumn.Insert
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 4)
    End With
    
    With destSht
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
    
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = srcSht.Range("K" & srcRowFirst).Value
    rngCrit.Cells(2, 1).Value = "="
    rngCrit.Cells(1, 2).Value = srcSht.Range("B" & srcRowFirst).Value
    rngCrit.Cells(2, 2).Value = "<>"
    rngCrit.Cells(1, 3).Value = srcSht.Range("I" & srcRowFirst).Value
    rngCrit.Cells(2, 3).Value = "<>"
    rngCrit.Cells(1, 4).Value = srcSht.Range("I" & srcRowFirst).Value
    rngCrit.Cells(2, 4).Value = "<>0"
    
    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.EntireColumn.Delete
    
    ' Set rngDest = rngDest.CurrentRegion
    ' rngDest.Columns.AutoFit               ' Use OPs setting instead
    
    With destSht
        .Columns("B:C").EntireColumn.AutoFit
        .Columns("E:K").EntireColumn.AutoFit
        .Columns("D:D").ColumnWidth = 49.57
        .Range("F:F,G:G,H:H,I:I").Style = "Comma"   ' If omitted will default to format on source sheet
        .Columns("K:K").NumberFormat = "m/d/yyyy"   ' If omitted will default to format on source sheet
    End With
    
End Sub

 

[mazher]

Extremely thankful to you @Alex Blankenburg for all your time, and help and for providing me with the solution to my problem.

 

[Alex Blakenburg]

Glad I could help. Thanks for letting me know that it is working for you.

 

[mazher]

Sorry for bothering you again @Alex Blakenburg

I just realized that in the Pending sheet the code is still copying the rows where the cell value in Column I are text.

 

[Alex Blakenburg]

I have logged off for the night can you send an XL2BB with examples and I will have a look tomorrow.

 

[mazher]

Hi @Alex Blakenburg ,

Thanks for bothering you again and again.

I have pasted the code

Pending Summary Testing1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD	Date
2	1001	ABC-FEB15-001	AA212	WWNFKP	1	£60.00	£60.00	£12.00	£72.00	01st February 2015 - 28th February 2015	11/12/2015
3	1001	ABC-FEB15-002	BB212	VRSILB	1	£98.00	£98.00	£19.60	£117.60	01st February 2015 - 28th February 2015	11/12/2015
4	1001	ABC-FEB15-003	CC212	GHHQDH	1	£85.55	£85.55	£17.11	£102.66	01st February 2015 - 28th February 2015	11/12/2015
5	1001	ABC-FEB15-003	CC212	KCKCSS	1	£65.95	£65.95	£13.19	£79.14	01st February 2015 - 28th Febraury 2015	11/12/2015
6	1001	ABC-FEB15-004	DD212	AGEJFR	1	£82.00	£82.00	£16.40	£98.40	01st February 2015 - 28th February 2015
7	1001	ABC-FEB15-005	EE212	AGNMLJ	1	£39.75	£39.75	£7.95	£47.70	01st February 2015 - 28th February 2015
8	1001	ABC-FEB15-006	FF212	MYQGPU	1	£69.75	£69.75	£13.95	£83.70	01st February 2015 - 28th February 2015
9	1001	ABC-FEB15-007	GG212	RRMCJL	1	£64.30	£64.30	£12.86	£77.16	01st February 2015 - 28th February 2015
10	1001	ABC-FEB15-008	HH212	VFIOWT	1	£49.90	£49.90	£9.98	£59.88	01st February 2015 - 28th February 2015	11/12/2015
11	1001	ABC-FEB15-009	II212	RQADLX	1	£114.00	£114.00	£0.00	£114.00	01st February 2015 - 28th February 2015
12	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
13	1001	ABC-FEB15-010	JJ212	OCQPBP	1	£78.49	£78.49	£0.00	£78.49	01st February 2015 - 28th February 2015
14	1001	ABC-FEB15-011	KK212	YDBZOG	1	£108.00	£108.00	£21.60	£129.60	01st February 2015 - 28th February 2015
15	1001	ABC-FEB15-012	LL212	EVSHVD	1	£89.00	£89.00	£0.00	£89.00	01st February 2015 - 28th February 2015
16	1001	ABC-FEB15-012	LL212	HMFAEK	1	£89.00	£89.00	£0.00	£89.00	01st February 2015 - 28th Febraury 2015	11/12/2015
17	1001	ABC-FEB15-013	MM212	RDVBQO	1	£120.00	£120.00	£0.00	£120.00	01st February 2015 - 28th February 2015	11/12/2015
18	1001	ABC-FEB15-013	MM212	FYCZPP	1	£120.00	£120.00	£0.00	£120.00	01st February 2015 - 28th Febraury 2015	11/12/2015
19	1001	ABC-FEB15-014	NN212	BWJFLR	1	£58.00	£58.00	£0.00	£58.00	01st February 2015 - 28th February 2015	11/12/2015
20	1001	ABC-FEB15-015	OO212	HJYCAR	1	£85.00	£85.00	£0.00	£85.00	01st February 2015 - 28th February 2015	11/12/2015
21	1001	ABC-FEB15-016	PP212	FJYEKV	1	£78.00	£78.00	£0.00	£78.00	01st February 2015 - 28th February 2015	11/12/2015
22	1001	ABC-FEB15-016	PP213	YWUBKL	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015	11/12/2015
23	1001	ABC-FEB15-016	PP212	ZJWDTG	1	£145.00	£145.00	£0.00	£145.00	01st February 2015 - 28th February 2015	11/12/2015
24	1001	ABC-FEB15-017	QQ212	CAGRZP	1	£79.00	£79.00	£0.00	£79.00	01st February 2015 - 28th February 2015	11/12/2015
25	1001	ABC-FEB15-018	RR212	DIAQJA	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015
26	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
27	1001	ABC-FEB15-018	RR212	SACTYF	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015
28	1001	ABC-FEB15-019	SS212	IVKFEB	1	£115.00	£115.00	£0.00	£115.00	01st February 2015 - 28th February 2015	11/12/2015
29	1001	ABC-FEB15-020	TT212	FKTQTD	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015	11/12/2015
30	1001	ABC-FEB15-020	TT212	HJRXYN	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
31	1001	ABC-FEB15-020	TT212	MSFXJS	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
32	1001	ABC-FEB15-020	TT212	CJQNNJ	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
33	1001	ABC-FEB15-020	TT212	UTPZNX	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
34	1001	ABC-FEB15-021	UU212	HQYYPP	1	£100.00	£100.00	£0.00	£100.00	01st February 2015 - 28th February 2015
35	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
36	1001	ABC-FEB15-022	TT212	MSFXJS	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
37	1001	ABC-FEB15-023	TT212	CJQNNJ	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
38	1001	ABC-FEB15-024	TT212	CJQNNJ	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
39	1001	ABC-FEB15-025	TT212	UTPZNX	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015	11/12/2015
40	1001	ABC-FEB15-026	UU212	HQYYPP	1	£100.00	£100.00	£0.00	£100.00	01st February 2015 - 28th February 2015	11/12/2015
Data Inv

Cell Formulas
Range	Formula
G2:G11,G36:G40,G27:G34,G13:G25	G2	=F2*E2
H2:H10,H14	H2	=G2*20%
I2:I11,I36:I40,I27:I34,I13:I25	I2	=G2+H2
H11,H36:H40,H27:H34,H13,H15:H25	H11	=G11*0%
F10	F10	=24.95*2

I dont need these rows marked in yellow in the "Pending" Sheet

Pending Summary Testing1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD	Date
2	1001	ABC-FEB15-004	DD212	AGEJFR	1	82.00	82.00	16.40	98.40	01st February 2015 - 28th February 2015
3	1001	ABC-FEB15-005	EE212	AGNMLJ	1	39.75	39.75	7.95	47.70	01st February 2015 - 28th February 2015
4	1001	ABC-FEB15-006	FF212	MYQGPU	1	69.75	69.75	13.95	83.70	01st February 2015 - 28th February 2015
5	1001	ABC-FEB15-007	GG212	RRMCJL	1	64.30	64.30	12.86	77.16	01st February 2015 - 28th February 2015
6	1001	ABC-FEB15-009	II212	RQADLX	1	114.00	114.00	-	114.00	01st February 2015 - 28th February 2015
7	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
8	1001	ABC-FEB15-010	JJ212	OCQPBP	1	78.49	78.49	-	78.49	01st February 2015 - 28th February 2015
9	1001	ABC-FEB15-011	KK212	YDBZOG	1	108.00	108.00	21.60	129.60	01st February 2015 - 28th February 2015
10	1001	ABC-FEB15-012	LL212	EVSHVD	1	89.00	89.00	-	89.00	01st February 2015 - 28th February 2015
11	1001	ABC-FEB15-018	RR212	DIAQJA	1	160.00	160.00	-	160.00	01st February 2015 - 28th February 2015
12	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
13	1001	ABC-FEB15-018	RR212	SACTYF	1	160.00	160.00	-	160.00	01st February 2015 - 28th February 2015
14	1001	ABC-FEB15-020	TT212	HJRXYN	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
15	1001	ABC-FEB15-020	TT212	MSFXJS	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
16	1001	ABC-FEB15-020	TT212	CJQNNJ	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
17	1001	ABC-FEB15-020	TT212	UTPZNX	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
18	1001	ABC-FEB15-021	UU212	HQYYPP	1	100.00	100.00	-	100.00	01st February 2015 - 28th February 2015
19	PO	INVOICE NO	CONTRACT	SCHOOL	DAYS	RATE	G Total	VAT	N Total	PERIOD
20	1001	ABC-FEB15-022	TT212	MSFXJS	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
21	1001	ABC-FEB15-023	TT212	CJQNNJ	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
22	1001	ABC-FEB15-024	TT212	CJQNNJ	1	186.00	186.00	-	186.00	01st February 2015 - 28th February 2015
Pending

I don't want to copy Values in Column I (N Total are zero or empty or Text)

Thanks again for all your help.

 

[Alex Blakenburg]

I can do a generic text exclusion for Column N but it will be more complicated than just excluding the additional heading eg Invoice No column <>"INVOICE NO".
Is it likely that the only text values in Column N are going to be headings repeating ?

 

[mazher]

Thanks, @Alex Blakenburg

You are right, the only text values in Column N are headings that are repeating.

 

[Alex Blakenburg]

OK Additional criteria added.

Sub CopyPasteUnpaid()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
       
    Set srcSht = Worksheets("Data Inv")
    Set destSht = Worksheets("Pending")
    srcRowFirst = 1
        
    With srcSht
        srcRowLast = srcSht.Cells(Rows.Count, "A").End(xlUp).Row
        srcColLast = srcSht.Cells(srcRowFirst, Columns.Count).End(xlToLeft).Column
        Set rngSrc = .Range(.Cells(srcRowFirst, "A"), .Cells(srcRowLast, srcColLast))
        ' Temporary range for criteria
        .Columns(srcColLast + 2).Resize(, 5).EntireColumn.Insert
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 5)
    End With
    
    With destSht
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
    
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = srcSht.Range("K" & srcRowFirst).Value
    rngCrit.Cells(2, 1).Value = "="
    rngCrit.Cells(1, 2).Value = srcSht.Range("B" & srcRowFirst).Value
    rngCrit.Cells(2, 2).Value = "<>"
    rngCrit.Cells(1, 3).Value = srcSht.Range("B" & srcRowFirst).Value
    rngCrit.Cells(2, 3).Value = "<>INVOICE NO"
    rngCrit.Cells(1, 4).Value = srcSht.Range("I" & srcRowFirst).Value
    rngCrit.Cells(2, 4).Value = "<>"
    rngCrit.Cells(1, 5).Value = srcSht.Range("I" & srcRowFirst).Value
    rngCrit.Cells(2, 5).Value = "<>0"
    
    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.EntireColumn.Delete
    
    ' Set rngDest = rngDest.CurrentRegion
    ' rngDest.Columns.AutoFit               ' Use OPs setting instead
    
    With destSht
        .Columns("B:C").EntireColumn.AutoFit
        .Columns("E:K").EntireColumn.AutoFit
        .Columns("D:D").ColumnWidth = 49.57
        .Range("F:F,G:G,H:H,I:I").Style = "Comma"   ' If omitted will default to format on source sheet
        .Columns("K:K").NumberFormat = "m/d/yyyy"   ' If omitted will default to format on source sheet
    End With
    
End Sub