copy cell to another cell, increment and copy back.

Hi Folks,

Ill apologise straight up as Im sure this is pretty simple, but I have been playing with this for hours and searching the net with no luck yet.

I am trying to make a purchase order spreadsheet. As a part of this I want the purchase order number to increment each time the master is used - intention is to use the master to fill in details, then save it as another sheet. Values such as PO number and date can increment in the master but not in the saved copy.

So I want to have a cell (J2) with a static value (starting at 0).
When a button is pressed, the macro copies the value in J2 to cell G2, increments the value by +1 and then copies the returned value in G2 back to J2.
At the end of the process above cell G2 will show '1' and cell J2 will show '1'.

Ive managed to do this with the date range. I entered the value =TODAY() into cell Z1 and used the following macro
Range("Z1").Select
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But this obviously does not help with my sum function for the purchase order number.

Thank you

Hi
is this what you're after:

Please Login or Register  to view this content.

Thank you very much NickyC thats fits perfectly!

Is there an easy way to have this data in another workbook and access it from there?
The trouble with the above is that I forgot to take into account that when the file is saved the original will not increment the order number. So it seems I will need to store the order number in a separate workbook and increment it to and from there.

All code Ive seen for this appears to require fairly lengthy if and with statements.
Is there a simple way to increment this cell in another workbook, then copy the result to this workbook?

Thank you.

you can, but it is probably not the easiest way to do what you're trying to do.

If you want to generate copies of the master for each purchase order, why not have a macro that increases the purchase order automatically in the master, saves it as the master, then creates a copy of the master and saves it with a different name as your purchase order.

If that's not what you're trying to do, you could try posting a copy of your workbook.

Thank you NickyC, good idea.

I really want to try to lock this down so that its hard to break. People here are not computer literate and this is why I wanted to have date and Purchase order as auto fill fields that they cannot screw up.

I like your idea of incrementing the number and then saving the master, Im working on how to do this but figured I should post the workbook here in case there is stuff I am doing wrong.
its been a long time since I have played with Excel formatting and VBA and its not like riding a bike

Thanks

looks like you're on the right track
the macros are fairly simple, you may need to think about sequencing - at what point do you want to change the order number, for example. Also, do you want to clear all the ext from the previous
Let me know if you need some help with the macros

ah - should have refreshed before posting -I'll look at it now

Seems fine. I didn’t test the save macro as the file paths are obviously set up for your directories.

A couple of tips (not necessary, just simplifying):

In Macroaaaaa:
Rather than entering range addresses it can be helpful to name ranges in the sheet and then use these for your clear contents macros. This means that if you add or remove lines and columns etc the macro should still work.

To do this, hold down the control key and use the mouse to select the ranges to be cleared (this allows non-adjacent cells to be selected), and enter a name like clear_range into the address box

Then you can run a single line of code

Please Login or Register  to view this content.

In Call_Today:
No need to hide the date in the sheet, a simple:

Please Login or Register  to view this content.

Will do

Again, this reduces the risk of your macros not working if the sheet is altered

Thank you very much NickyC, that all works really well now. I have applied your suggestions (kinda, I couldnt work out your clear_range part, but instead just used the whole range to clearcontents which is much nicer than I had.
I had thought the date() woudlnt work as I thought it would place the formula in the cell, but this isnt the case so it works great as you have proposed.

Thanks again, I really appreciate your help. Cheers!