Auto Number Copy from one Workbook to another!

[eddy]

I have two Workbooks (Quote.xls and number.xls)
Quote.xls contains a Worksheet called CopySheet .
Cell C8 in CopySheet contains a number (say 1000)
which is generated from Sheet1 Cell A1 in Number.xls.
We have Quote.xls open
I need a macro in Workbook Quote.xls which will :-

a) Open number.xls (file location is q:newquotesystem)
b) Look at Sheet 1 Cell A1 .
c) Increment the number in Cell A1 by 1
d) Copy the contents of A1 to Cell C8 in worksheet "CopySheet" in
Workbook Quote.xls
e) Save & Close Workbook Number.xls
f) Return to CopySheet in Workbook Quote.xls

All help appreciated Ted

[Tom Schreiner]

Hi Ted
I did not test this, but see if it works for you.
Cheers!

Sub TedsSub()
Workbooks.Open "q:newquotesystemNumber.xls"
Sheet1.Range("A1").Value = Sheet1.Range("A1").Value + 1
Workbooks("Quote.xls").Sheets("CopySheet").Range("C8").Value = _
Sheet1.Range("A1").Value
ActiveWorkbook.Close savechanges:=True
Sheets("CopySheet").Activate
End Sub

[nisht]

Sub assignvalue()

Application.ScreenUpdating = False
Workbooks.Open Filename:="c:my documentsnumbering.xls"
Workbooks("numbering").Activate
Worksheets("sheet1").Select
workbooks("numbering").worksheets("sheetname").select
[a8] = [a8]+1
range("a8").EntireRow.Copy Workbooks("quote").Worksheets("Sheet1").Range(c8)
Workbooks("numbering").Close

Application.ScreenUpdating = True
End Sub

I have not checked the code...

but it should work.

if you have problem then.. write back to me.

nishith deai
http://www.pexcel.com

[Tom Schreiner]

On 2002-04-20 10:51, TsTom wrote:
Hi Ted
I was at work and very tired... Slow night.
If all you need is a "shared counter" then don't use a workbook...
Too much overhead and maybe some "workbook in use use problems?"

I would just throw a little random access file onto your shared drive.
This type of file can be opened by any number of people at the same time.

Place this code in your project and call it as needed...

Sub GetQuoteNumber()

Dim TedsNumber As Long
Dim FileNum As Integer

'gets any available file number
FileNum = FreeFile

Open "q:newquotesystemQuoteNumber.txt" For Random As #FileNum Len = Len(TedsNumber)
'gets the current value
Get #FileNum, 1, TedsNumber
'increments by one
TedsNumber = TedsNumber + 1
'places the new value to file
Put #FileNum, 1, TedsNumber
Close #FileNum

'places the new value to copysheet
Sheets("CopySheet").Range("A1").Value = TedsNumber

End Sub

Will post it as well...
Cheers!
Tom