PDA

View Full Version : Archiving Rows on Excel using VBA



Corrina
05-22-2017, 08:39 PM
Hi,
This is my first time posting so please be gentle!

I have two sheets relating to invoices named Current and Paid.

I'd like to do a few things

1. When I enter data into the sheet named Current, it highlights the row to remind me it's to be paid.

2. When I enter a date into the the Paid column of the worksheet (column 9):

a) the highlight is removed
b) the row is moved to the next available row in the worksheet named Paid and the blank row created in the worksheet named Current is deleted (data moved up).

I can highlight and remove the highlight using conditional formatting using COUNTIF however am unsure of what to write to read a date (1/1/17) rather than text.

Any assistance will be greatly appreciated.

Many thanks,
Corrina

Bob Phillips
05-23-2017, 01:28 AM
Corrina, you would be far better off to have just one table with all items. They are all invoices, they are just in a different state of not paid or paid, so splitting just adds unnecessary complexity to your spreadsheet.

You can identify the paid because they will have a date. If you add filters to the columns, you can filter on anything, customer, amount, and of course, paid invoices.

mdmackillop
05-23-2017, 03:58 AM
I can highlight and remove the highlight using conditional formatting using COUNTIF however am unsure of what to write to read a date (1/1/17) rather than text.
Make sure your column is formatted as date e.g. NumberFormat = "dd/mm/yyyy"
Select the column and use DateValue in the formula.

For more on Conditional Formatting, visit Contextures (http://www.contextures.com/xlCondFormat01.html)

Corrina
05-24-2017, 10:12 PM
Thank you xld and mdmackillop will try it and see how I go :)

Bob Phillips
05-25-2017, 01:26 AM
Do let us know what you try and how it goes. Don't just accept whatever you get, there is always round 2 :-).

mykal66
05-28-2017, 12:08 AM
Hi Corrina.

I have a test workbook that some of the guys on here helped me with in the past that will move the rows to the appropriate worksheet once marked as paid - attached

I've used it on several workbook at work and the code is simple to follow

Hope it helps

Mykal

19308

Corrina
05-29-2017, 10:44 PM
Hi Corrina.

I have a test workbook that some of the guys on here helped me with in the past that will move the rows to the appropriate worksheet once marked as paid - attached

I've used it on several workbook at work and the code is simple to follow

Hope it helps

Mykal

19308

Wow mykal66 (66 is a good year by the way!)

I've copied my data into the spreadsheet you attached and it works like a charm!!

Many thanks
Corrina