• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add Recurring Transactions Each Month

Tech56

Member
Hi,

On the Transactions sheet there is a green shape 'Add Recurring Transactions' which runs code to add rows to the register from the Recurring Log sheet.

Is there a way to prevent it from running again if they have been added already for this month? Maybe a popup message too saying "You have already added these recurring transactions for this month".

Thank you in advance for your help
 

Attachments

vletm

Excel Ninja
Tech56
Do You mean 'MoveTransX' ?
If You Move then there should be after Move those rows ONLY ONCE!
1) copy needed rows from 'Transactions'-sheet
2) paste those rows to 'Recurring Log'-sheet
3) clear those rows away from 'Transactions'-sheet.
 

Tech56

Member
I click on the shape to launch the code to copy and paste the rows each month. I am asking to alter the code so that it can't happen more than once in each month thanks.
 

Tech56

Member
Maybe you are not understanding me so I will try to post this in the Excel Guru forum.

 

vletm

Excel Ninja
Tech56
If You do this part
3) clear those rows away from 'Transactions'-sheet.
- then it won't worry about duplicates anymore!
 

Tech56

Member
I don't think you understand.

The Recurring Log sheet has transactions that occur every month. These need to be included in the Transactions sheet each month but not more than once each month.
 

Tech56

Member
It is set to add the entries when clicking the shape. This works fine but I want to prevent adding these entries if they have already been added in that month.

Thanks
 

vletm

Excel Ninja
Tech56
It works AFTER You'll ADD next step
3) clear those rows away from 'Transactions'-sheet.
to that 'MoveTransX' which I offered for You with #2 reply - the last line.
> Yes, no need to read other replies <
 

vletm

Excel Ninja
Tech56
For testing purpose
I copied those TWO sheets in the end of this workbook - use those now.
I also did NEW light green button for testing.
You had 'hidden' some rows - not 'handy' ... but I could continue...
Press that 'Light Green'-button ...
after that ... You cannot do duplicate adding as I have written many times.
 

Attachments

Tech56

Member
Thanks but it still makes more copies each time it is selected. I think you aren't understanding.

The Transactions sheet is where they need to go.

The Recurring Log sheet will remain the same. These are the fixed amounts each month that need to be posted to the Transactions sheet.
 

Tech56

Member
This has caused you too much trouble and for that I am sorry.

I will stay with my original workbook and make sure I don't click to add more than one time each month.

Thank you
 

vletm

Excel Ninja
Tech56
Did You tested it as I wrote ... NO!
For testing purpose
I copied those TWO sheets in the end of this workbook - use those now.
I also did NEW light green button for testing.
You had 'hidden' some rows - not 'handy' ... but I could continue...
Press that 'Light Green'-button ...
after that ... You cannot do duplicate adding as I have written many times.

This would gone much smoother, if You could do as someone else have given instructions - but no!
That is only challenge for me with this thread!
 

vletm

Excel Ninja
Tech56
For testing purpose,
For testing purpose,
For testing purpose,
For testing purpose!
with minor changes

You could get it work with ANY sheets.

I need to do that way - because I need to test it!
 

Attachments

Tech56

Member
Thanks for trying but I will stay with what is working. I am not sure why my original workbook code can't have a line checking for previous entries if they have been posted yet.

Coding is beyond my capabilities at the moment but logically it doesn't seem to be that difficult of a request.

Sorry for your trouble.
 

NoSparks

New Member
As I understand the OP, why not store the date when the macro is run and at the beginning of the macro compare month of current date with month of stored date ? This stores to F1 to be behind the shape.
Code:
Sub MoveTransX()
    Dim i As Long, lr As Long, lr2 As Long
    Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Recurring Log")
Set s2 = Sheets("Transactions") 
'check if already done for this month
If Month(Date) = Month(Range("F1")) Then
    MsgBox "You have already added these recurring transactions for this month"
    Exit Sub
Else
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 4 To lr
        If s1.Range("B" & i) <> "" Then
            s1.Range("A" & i & ":I" & i).Copy
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    'store when last run
    s2.Range("F1") = Date
    MsgBox "complete"
End If
End Sub
 
Top