ASSIGNING A NEW NUMBER TO A WORKSHEET EVERY TIME IT IS OPEN

S

steppie70

is there a way each time you open a worksheet the number advances, for
instance, we are using the excel program to generate bill of ladings for our
company, we need new numbers every time the b.o.l. is opened to insure we do
not use the same number twice, we would like to have a formula that does this
for us.
 
K

kevindmorgan

Substitute the worksheet and cell value and it will count up each time
it is opened.

Sub Auto_Open()
Sheets("whicheversheet").select
Cells(1, 1).Value = Cells(1, 1).Value + 1
End Sub
 
G

GerryK

You could use this simple macro in Sheet 1 for cell A1 to advance. You need
to save in order for the number to increase next time.

Private Sub Workbook_Open()
Worksheets("Sheet1").Select
Range("A1").Value = Range("A1") + 1
End Sub

VBA placed in Sheet 1 (Alt F11 then paste the code.
 
S

steppie70

I very much appreciate this information, but I am a bit of a excel dummy,
could you give me the proper steps to do this.
 
K

kevindmorgan

Sure thing!

Edit the macro to fit your needs as follows:

1. Replace "whicheversheet" with the name of your worksheet.
2. Replace "Cells(1,1)" (both instances) with whatever address you
want to use on your worksheet to count. The first number is the row,
the second is the column. In my case, it references A1.
3. Open MS VB Editor. (Tools>Macros>Visual Basic Editor)
4. Click Insert>Module
5. Copy and paste the macro in the module (edit as stated above if you
haven't already) and close VB Editor.
6. Save the workbook, close it and open it again. Each time you close,
be sure to save and whenever you open it, the referenced cell value
will increase by one.

Let me know how yah did!

Kevin

=======edit the macro!!============

Sub Auto_Open()
Sheets("whicheversheet").select
Cells(1, 1).Value = Cells(1, 1).Value + 1
End Sub

=====end copy====================
 
S

steppie70

THANK YOU THANK YOU THANK YOU, THAT WORKED, NOW I HAVE ANOTHER FEW QUESTIONS.
This is all to make a Bill of Lading with having as little as information to
type as possible.

1. I would like to know how to pick a Company name and only it's part
numbers come up. For instance: In my ship to box, I will click on my drop
down list, pick Company # 1, then down in my bill of lading body only have
the parts that we make for Company #1 show up in a drop down list.

2. Also, have the address, bill to and things of that nature come up with
the click of one button.

If me asking all this information is a bit much, Please don't hestitate to
let me know.
 
K

kevindmorgan

I have a file that does this very thing...I made it for another user on
here. If you'd like, I could send it to you. Email me at my yahoo
address. Use the same username as I have on here. ;-)
 
Top