Auto Number Copy from one Workbook to another!

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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.

ni****h deai
http://www.pexcel.com
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top