Copy worksheet by formula

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Hi, I have a question regarding my excel worksheet.

I have a worksheet 2 that contain details of account that due daily as per below.
______________________________________________________________________________
Entry date Cust Ref No. Due date Debit Credit
01-Jun-10 A 1 3-Jun-10 100.00
A 2 3-Jun-10 250.00
B 8 2-Jun-10 256.00

02-Jun-10 B 8 2-Jun-10 256.00
02-Jun-10 C 5 9-Jun-10 200.00
______________________________________________________________________________

May I know on how to transfer account details that not yet due to worksheet 1 just like below by using excel function / formula only. (I don't know how to use VBA code)
______________________________________________________________________________
Entry date Cust Ref No. Due Date Amount
01-Jun-10 A 1 3-Jun-10 100.00
A 2 3-Jun-10 250.00
02-Jun-10 C 5 9-Jun-10 200.00

Hope anybody out there can help me.
Thank you for your help

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
hard to see your data here. Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
thank u for ur reply. i already upload the workbook as u can see below
https://authentification.site/files/23026821/Book2.xls

as u can see frm the workbook, i have 2 sheet in it.
1) Daily a/c due - key in all the invoice that cust make early payment in that current month (in this case is in June) and date when exactly the invoice is due (under due date column).
while the "entry date" colum indicating when the payment rec'd from cust

2) Monthly Reconcile a/c - summary of invoice that not yet due but cust already make early payment on it. this data come from "daily a/c due" sheet

i really hope there is a way to make:
1) to transfer account details that not yet due to worksheet "Monthly Reconcile" by using excel function / formula only. (I don't know how to use VBA code / macro), such as by using IF function

2) i have use conditional formating on column D in "Daily a/c due" worksheet, to remind me the due a/c on next day, but it only highlight the colum D.
How can i make it highlight all the row that due on the next day.

Thank you
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok first answer to your two question
#1: you have to use macro
#2: keep your formula as before but select the rows. For example select all rows and the go to conditional formatting and apply the =INT($D:$D)=TODAY() By using $ sign you force the evaluation of condition based on column D for every cell

Now to my confusion. I am confused over your book and explanation.
take #2.
You say that "Monthly Reconcile a/c " shows those records for which the invoice is not due and yet customer makes a payment. On that sheet you show two rows for ref# 9 and 10 for amount 230 and 269. This payment was made on June 3. Now if I look at the detail sheet, I see that for #9 there is a date but for #10 there is not any date. Also I see the for #1 there is a date yet it does not show on your summary sheet. Could you go more into detail to break down the how data show up on the other sheeet
In the "Monthly Reconcile" worksheet, i already sort it out based on entry date. The row in between just add-up to make the worksheet look nice. Basically the data in monthly reconcile is sort based on entry date and followed by due date.

the thing that i look for is just like what u said, except it make the due date as reference. Meaning that everything that due more than 30/06/2010 (based on this workbook) is consider as pending reversed and will be copied in "Monthly Reconcile" worksheet


thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If any thing that before 30/06/2010 is to be on recon sheet, then ref#1 should be too. I think what you mean to say is that

for all items that are for the current month as far as due date is concerned and whose entry date is more than today's date need to go to recon

and

for all items on recon sheet that are before today's date or of today's date, should be removed from recon and appear as reversed in the data sheet.

Again wrong ?
basically on the recon sheet i will key in this kind of item:

every early payment that i received on the month of june for invoices that due after june, i will put in recon sheet. For example : i received payment on 20/06/2010 for invoice dated 03/07/2010, then i will key in the data in recon sheet.

thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok I think we are talking past each other. Let me try to ask you in other way

This pertains to RECON sheet

This is my understanding of your recon sheet. At any given day, once you have done reversing, the recon sheet will show all the records for which payments that has been made earlier. Earlier than Due date. This date could be a day, a month a year early etc, as long as the data is on the other sheet.

On the recon, no data exists for which the due date is earlier than the current calendar date. So if today is June 23, 2010. Any and all data once reverse enteries have been made, will be for due date of June 24 2010 and later. Nothing from June 23, 2010 and earlier would be on the recon sheet (After the reversing)

If that is not the case then I will circle back to my initial question, Why data of june 1 was removed and june 4 was not when for both early payment was made.
Yes, the first one is correct. That why data that already been reversed, will be taken out and only data that pending reverse will be enter on the recon sheet.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok Try this. This will recreate the data rows in the recon sheet.

Sub ReCreateRecon()

Dim lMaxRows As Long
Dim sTempSheet As String
Dim sDaily As String
Dim sRecon As String
Dim lDailyStartRow As Long
Dim lReconStartRow As Long
Dim lReconNewRow As Long

    sTempSheet = "my_temp_sheet"
    sDaily = "Daily AC Due"
    sRecon = "Monthly Reconcile"
    lRecConStartRow = 14
    lDailyStartRow = 2
    
    Sheets(sDaily).Select
    ActiveSheet.AutoFilterMode = False
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Select
    Selection.AutoFilter FIELD:=1, Criteria1:="<>"
    Selection.AutoFilter FIELD:=4, Criteria1:=">" & Date
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    If (lMaxRows < lDailyStartRow + 1) Then GoTo End_Sub
    
    On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(sTempSheet).Delete
        Application.DisplayAlerts = True
        Err.Clear
    On Error GoTo Error_Handle
        
    Sheets.Add
    ActiveSheet.Name = sTempSheet
    
    Sheets(sDaily).Select
    Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Copy
    
    Sheets(sTempSheet).Select
    Range("A1").PasteSpecial
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(1, "A"), Cells(lMaxRows, "G")).Sort _
                    Key1:=Range("A2"), order1:=xlAscending, _
                    Key2:=Range("C2"), order2:=xlAscending, _
                    Header:=xlYes

   
    lReconNewRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Sheets(sRecon).Select
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row - 3
    
    Rows(lRecConStartRow & ":" & lMaxRows).ClearContents
    
    Rows(lRecConStartRow + 1 & ":" & lMaxRows - 1).Delete
    
    Sheets(sTempSheet).Select
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(2, "A"), Cells(lMaxRows, "E")).Copy
    
    Sheets(sRecon).Select
    
    
    Range("A" & lRecConStartRow + 1).Select
    Selection.Insert Shift:=xlDown
    
    lMaxRows = lRecConStartRow + 1
    Do While (Cells(lMaxRows, "A") <> "")
        
        If (Cells(lMaxRows, "A") <> Cells(lMaxRows + 1, "A")) Then
        
            Rows(lMaxRows + 1).Insert
            lMaxRows = lMaxRows + 1
            
        End If
        
        lMaxRows = lMaxRows + 1
    Loop
    
  

End_Sub:

    Sheets(sDaily).Select
    ActiveSheet.AutoFilterMode = False
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Select
    Selection.AutoFilter
    Range("A" & lDailyStartRow).Select
    
    On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(sTempSheet).Delete
        Application.DisplayAlerts = True
        Err.Clear
    On Error GoTo 0
    
    Exit Sub

Error_Handle:

    MsgBox Err.Description
    GoTo End_Sub

    
End Sub