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

[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.

[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

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.

[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.

"steppie70" wrote:

> 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.

[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.

"kevindmorgan" wrote:

>
> 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
>
>
> steppie70 Wrote:
> > 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.
>
>
> --
> kevindmorgan
> ------------------------------------------------------------------------
> kevindmorgan's Profile: http://www.excelforum.com/member.php...o&userid=32232
> View this thread: http://www.excelforum.com/showthread...hreadid=520290
>
>

[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====================

[QUOTE=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.

[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.


"kevindmorgan" wrote:

>
> 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====================
>
> steppie70 Wrote:
> > 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.
>
>
> --
> kevindmorgan
> ------------------------------------------------------------------------
> kevindmorgan's Profile: http://www.excelforum.com/member.php...o&userid=32232
> View this thread: http://www.excelforum.com/showthread...hreadid=520290
>
>

[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. ;-)



[QUOTE=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.