Next Available Number

[AnnieLaurie]

I need to make a macro which will go to the PO numbers sheet and retrieve the next available PO Number and return it to the Purchase Order Sheet
Then I would like a macro to take the pertinent info off the Purchase Order Sheet and paste in into the PO number sheet.. Is this able to be done?

[Pete_UK]

Take a look at John McGimpsey's site here:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

for two ways of doing it.

Hope this helps.

Pete

[mrice]

Please see the attachment for an example.

It requires that the column headings and field names are identical.

[AnnieLaurie]

Your macro runs beautifully but I can't seem to adapt it to my sheets
Could you please make the changes necessary?

On Purchase Order Sheet

PO Number is in cell R5 Col E
Date is in cell R6 Col E & F
Vendor is in cell R11 Col B & C
Total Cost is in cell R39 Col G
Description is in cell R21 Col C & D

On PO Number Sheet

The Cells are just as your sheet had them..

PO Number Column A
PO Date Column B
Vendor Column C
Total PO Column D
Job Number Column E
Description Column F

I figured out some of the changes but it crashes when it goes to put the info back

Anne

[mrice]

Does E & F etc mean that you have merged cells?

[AnnieLaurie]

Yes because under them are columns for job # and unit price

but we could just use the first cell for the date if it would run into the 2nd cell because one cell is not big enough for a full date format

[mrice]

Please have a look at the attached.

[AnnieLaurie]

Thank you very much... It works great... Am retired but just got a part time job in small fabricating office. It has been years since I worked with macros and I know what they can do but have forgotten how I did them... I hate to keep bothering you but I need to finish your macro with a copy PO to PO file and then clear template. I think I am ok with everything but how to get the macro to let you put in the correct PO number to the saved file.

[mrice]

Something like this might help.

Please Login or Register  to view this content.

[AnnieLaurie]

I made changes in your code... just small ones but I have 2 problems... One is where to save it.... I tried to use the path to our network and it would not work... It does save it to my C: drive...

The second problem is that I would like to save the "print area" not the whole sheet and also save the pretty colors and the Company Logo... I noticed that your code says delete drawing objects. Is this where you take off the color box and logo? I would like to leave it on the PO when it is saved... Also could we save it as a pdf? Can I send you my file so you can see the whole picture?

This is what the code looks like now:

Please Login or Register  to view this content.

I am going on vacation in 1/2 hour.... If I do not hear from you by the time I leave, I will get back to you when I return.. Thank you for all your help.

Anne

[NBVC]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[mrice]

The delete drawing objects was there to remove the button from the copied sheet - I was obviously unavare of other things that you had on there.

It would be a best if you could post a copy of the actual sheet.

[vitaminallan]

I was wondering the same thing actually thanks!

[NBVC]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

[AnnieLaurie]

How do I post a copy of the sheet in question?

PS I think I corrected the post that was not in compliance.

Anne

[NBVC]

Reply to Thread, click Go Advanced, and click the paperclip icon. Browse and upload your file, then close.

Add some words to the post and post the reply.

[AnnieLaurie]

I edited my post #10 with what I think is the correction... If I am wrong, please let me know how to fix it... This is my first question in this forum and am unsure how to proceed.

[AnnieLaurie]

AF Purchase Order w Macro.xlsm

Here is my spreadsheet... Hope it helps

[mrice]

Please see the attachment - hopefully its a bit closer to what you need.

[AnnieLaurie]

Please Login or Register  to view this content.

The bold part crashes and says "subscript out of range"

Plus could you save it as a pdf?

[mrice]

Is your sheet still called Purchase Order - I expect the error that you are seeing when a non-existent sheet is mentioned in the code. I also note that there is no \ afer the colon in your constant declaration for the save path.

wrt the PDF format, you will need something like

Please Login or Register  to view this content.

[AnnieLaurie]

I was using the spreadsheet that you sent back and the macro did not run. I inserted the PDF code but did not know where to insert it or if I should take any code out... My experience with Macros is to use the record button. Also I cannot assign macros on the worksheet you sent back.. Do I need to enable something.. I could always do that before.

[mrice]

Please see the attached. I've attempted to get the graphics to transfer to the PDF as well.

[AnnieLaurie]

Thank you Thank you Thank you.... It seems to work great... I will finish checking it out tomorrow but I feel YOU have done a commendable job and I truly appreciate it... Thanks again

[AnnieLaurie]

I tried to go to the first post and edit it to make it "solved" but I could not find a button for edit.

[mrice]

Mark your post [SOLVED] by editing your original post title in advanced mode. It's not very obvious.