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]

I have used macro recorder and got this following code

Sub CopyPasteData()
    
    Range("A1048575").Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A7190:K7335").Select
    Range("A7335").Activate
    Range(Selection, Cells(1)).Select
    Range("A1:K7335").Select
    Range("A7335").Activate
    Selection.Copy
    Sheets("Pending").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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").Select
    Selection.Style = "Comma"
    Columns("K:K").Select
    Selection.NumberFormat = "m/d/yyyy"
    
    Range("A1").Select
        
    Application.CutCopyMode = False
End Sub

I am unable to record a macro for deleting the rows in Sheet Pending where there is a date value in column K.

Please can someone help me clean this code and kindly add the above task.

Thanks in advance

 

[mazher]

Sorry for tagging
@Peter_SSs
@Alex Blakenburg

Please can you help with the above

Thanks in advance.

 

[Alex Blakenburg]

When you describe the requirement you need to give the real sheet names. Your text says

Sheet2 manually in Sheet7

Your code says Sheet7 is called Pending. If that is the case what is Sheet2 called.

Do you we clear the Pending Sheet each time the code runs and then from Sheet2 copy all the rows with column K = blank ?
And can we ignore the reference to Sheet2 being appended to and that you always want all unpaid rows from that sheet ?

 

[mazher]

Thanks for your time and reply @Alex Blakenburg

Sorry for the confusion

My main data sheet is "Data Inv"
I am want to copy data in the sheet named "Pending"

I want to clear all the data in the "Pending" sheet when the macro runs.
I want to copy all the rows from the "Data Inv" sheet where column K is blank ( is not paid)

Sorry for any miscommunication on my end in the previous posts.

 

[Alex Blakenburg]

See if this does what you need.

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).EntireColumn.Insert
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 1)
    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 = "="
    
    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.EntireColumn.Delete
    
    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]

Thanks, @Alex Blakenburg for your time.

When I am running the macro it is only copying the header row from the sheet "Data Inv" to the "Pending" sheet.

 

[Alex Blakenburg]

Then your column K is not being recognized as being blank. Please provide an XL2BB sample of your data ?
Is there a formula in column K, if yes at a minimum I need the formula.

 

[mazher]

Thanks @Alex Blakenburg for your time

I have provided the XL2BB for both sheets.
In column K when the invoice is paid I write the date its paid like 11/12/2015 (dd/mm/yyyy)

For Data Inv 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
2	1001	ABC-FEB15-001	AA212	WWNFKP	1	£60.00	£60.00	£12.00	£72.00	01st February 2015 - 28th February 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	11/12/2015
7	1001	ABC-FEB15-005	EE212	AGNMLJ	1	£39.75	£39.75	£7.95	£47.70	01st February 2015 - 28th February 2015	11/12/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	1001	ABC-FEB15-010	JJ212	OCQPBP	1	£78.49	£78.49	£0.00	£78.49	01st February 2015 - 28th February 2015
13	1001	ABC-FEB15-011	KK212	YDBZOG	1	£108.00	£108.00	£21.60	£129.60	01st February 2015 - 28th February 2015
14	1001	ABC-FEB15-012	LL212	EVSHVD	1	£89.00	£89.00	£0.00	£89.00	01st February 2015 - 28th February 2015
15	1001	ABC-FEB15-012	LL212	HMFAEK	1	£89.00	£89.00	£0.00	£89.00	01st February 2015 - 28th Febraury 2015	11/12/2015
16	1001	ABC-FEB15-013	MM212	RDVBQO	1	£120.00	£120.00	£0.00	£120.00	01st February 2015 - 28th February 2015	11/12/2015
17	1001	ABC-FEB15-013	MM212	FYCZPP	1	£120.00	£120.00	£0.00	£120.00	01st February 2015 - 28th Febraury 2015	11/12/2015
18	1001	ABC-FEB15-014	NN212	BWJFLR	1	£58.00	£58.00	£0.00	£58.00	01st February 2015 - 28th February 2015	11/12/2015
19	1001	ABC-FEB15-015	OO212	HJYCAR	1	£85.00	£85.00	£0.00	£85.00	01st February 2015 - 28th February 2015	11/12/2015
20	1001	ABC-FEB15-016	PP212	FJYEKV	1	£78.00	£78.00	£0.00	£78.00	01st February 2015 - 28th February 2015	11/12/2015
21	1001	ABC-FEB15-016	PP213	YWUBKL	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015	11/12/2015
22	1001	ABC-FEB15-016	PP212	ZJWDTG	1	£145.00	£145.00	£0.00	£145.00	01st February 2015 - 28th February 2015	11/12/2015
23	1001	ABC-FEB15-017	QQ212	CAGRZP	1	£79.00	£79.00	£0.00	£79.00	01st February 2015 - 28th February 2015	11/12/2015
24	1001	ABC-FEB15-018	RR212	CKPOPX	1	£89.00	£89.00	£0.00	£89.00	01st February 2015 - 28th February 2015
25	1001	ABC-FEB15-018	RR212	OBTKVQ	1	£180.00	£180.00	£0.00	£180.00	01st February 2015 - 28th February 2015
26	1001	ABC-FEB15-018	RR212	RLXQVO	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015
27	1001	ABC-FEB15-018	RR212	DIAQJA	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015
28	1001	ABC-FEB15-018	RR212	SACTYF	1	£160.00	£160.00	£0.00	£160.00	01st February 2015 - 28th February 2015
29	1001	ABC-FEB15-019	SS212	IVKFEB	1	£115.00	£115.00	£0.00	£115.00	01st February 2015 - 28th February 2015
30	1001	ABC-FEB15-020	TT212	FKTQTD	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
31	1001	ABC-FEB15-020	TT212	HJRXYN	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
32	1001	ABC-FEB15-020	TT212	MSFXJS	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
33	1001	ABC-FEB15-020	TT212	CJQNNJ	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
34	1001	ABC-FEB15-020	TT212	UTPZNX	1	£186.00	£186.00	£0.00	£186.00	01st February 2015 - 28th February 2015
35	1001	ABC-FEB15-021	UU212	HQYYPP	1	£100.00	£100.00	£0.00	£100.00	01st February 2015 - 28th February 2015
36
Data Inv

Cell Formulas
Range	Formula
G2:G35	G2	=F2*E2
H2:H10,H13	H2	=G2*20%
I2:I35	I2	=G2+H2
H11:H12,H14:H35	H11	=G11*0%
F10	F10	=24.95*2

For 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
2
3
4
5
6
Pending

 

[Alex Blakenburg]

Any reason your column K doesn't seem to have a heading ? If you give it a heading it will work fine.