Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Auto Number Copy from one Workbook to another!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •