I created a spreadsheet to track personal business expenses. I'm trying to eliminate 'user error' issues that have occurred in prior years by eliminating other people from needing to touch the monthly tab sheets. Mainly my boss. Every time he touches it to be 'helpful' he screws it up.
My first worksheet is named 'Entry'.
The data is entered on the first page using the following headings:
A1: Amount
B1: Expense Category - this is a drop down list
C1: Item Description (name of business/restaurant/etc)
D1: Purpose - drop down list
E1: Date is currently in E1 - I was trying to make it move to the appropriate sheet once the date was input but ended up using a macro because we tend to get receipts, etc a bunch at a time.
I have individual monthly worksheets with the same headings. I also have a 'Yearly' page that will eventually total up all the individual monthly categories so that we only need to look at that yearly page for accounting purposes.
The goal is to have the data entered on the 'Entry' page - and for them to simply hit the 'Add to Month' button and all the data is moved from the entry page to the appropriate month for that receipt.
I have two issues:
1.) It's skipping row 3 when it moves the data to the spreadsheet. I tried deleting that row - inserting a new row. It acts like it doesn't even exist. The coding doesn't skip over the row - it's included in the code. I can't figure out WHY it's doing it.
2.) It's moving the data that is in Row 6 to Row 6 of the matching month - instead of the first empty row.
code]Sub MoveData()
For Each c In Worksheets("Entry").Range("E2:E100").Cells ' Look at Rows 2-100 in ENTRY
EntryRow = c.Row ' this was part of my quest to find out why it skipped the second row of data, otherwise meaningless
If c.Text = "" Then Exit For ' quit early if no date in current row
Range(Cells(c.Row, 1), Cells(c.Row, 5)).Select ' Select the current row from ENTRY
Selection.Cut ' Cut the selection
Sheets(MonthName(Month(c.Text))).Select ' Identify target sheet from month value in column 5
Cells(EntryRow, 1).Activate ' Go to column 1 in target sheet
ActiveSheet.Paste ' Paste the cells in same row from ENTRY
Sheets("Entry").Select ' Go back to the ENTRY sheet
Application.CutCopyMode = False ' Clear the clipboard of previously copied cells
Next c ' Advance to next row
End Sub
[/code]
My first worksheet is named 'Entry'.
The data is entered on the first page using the following headings:
A1: Amount
B1: Expense Category - this is a drop down list
C1: Item Description (name of business/restaurant/etc)
D1: Purpose - drop down list
E1: Date is currently in E1 - I was trying to make it move to the appropriate sheet once the date was input but ended up using a macro because we tend to get receipts, etc a bunch at a time.
I have individual monthly worksheets with the same headings. I also have a 'Yearly' page that will eventually total up all the individual monthly categories so that we only need to look at that yearly page for accounting purposes.
The goal is to have the data entered on the 'Entry' page - and for them to simply hit the 'Add to Month' button and all the data is moved from the entry page to the appropriate month for that receipt.
I have two issues:
1.) It's skipping row 3 when it moves the data to the spreadsheet. I tried deleting that row - inserting a new row. It acts like it doesn't even exist. The coding doesn't skip over the row - it's included in the code. I can't figure out WHY it's doing it.
2.) It's moving the data that is in Row 6 to Row 6 of the matching month - instead of the first empty row.
code]Sub MoveData()
For Each c In Worksheets("Entry").Range("E2:E100").Cells ' Look at Rows 2-100 in ENTRY
EntryRow = c.Row ' this was part of my quest to find out why it skipped the second row of data, otherwise meaningless
If c.Text = "" Then Exit For ' quit early if no date in current row
Range(Cells(c.Row, 1), Cells(c.Row, 5)).Select ' Select the current row from ENTRY
Selection.Cut ' Cut the selection
Sheets(MonthName(Month(c.Text))).Select ' Identify target sheet from month value in column 5
Cells(EntryRow, 1).Activate ' Go to column 1 in target sheet
ActiveSheet.Paste ' Paste the cells in same row from ENTRY
Sheets("Entry").Select ' Go back to the ENTRY sheet
Application.CutCopyMode = False ' Clear the clipboard of previously copied cells
Next c ' Advance to next row
End Sub
[/code]
HTML:
ABCDEF1 Amount Expense CategoryItem DescriptionPurposeDate2 $ 123.15 Client DevelopmentDining PlaceClient Development1/12/20163 $ 146.00 Client GiftsJW Laredo2/15/20154 $ 25.00 Computer & Phone Equipment handsetsClient Development8/15/20155 $ 100.00 Contributions - CharitableLaredo2/15/20166 $ 75.00 Client GiftsLaredo3/1/20167 $ 4,034.00 Business EntertainmentSocial Care7/7/20168 $ 201.00 ParkingAHIC11/6/20169 $ 15.15 Contributions - Campaign/PoliticalClient Development12/31/20161011[CENTER][COLOR=#161120][B]Entry[/B][/COLOR][/CENTER]
