Data Update (From one workbook to another)
Data Update (From one workbook to another)
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Data Update (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 need to update costs prices in a workbook from a data export from Sage Accounts.
    I can save from Sage to an Excel workbook in the following format:-
    A B
    1 P/No Price
    2 SL6002 2.05
    3 RT1005 1.30
    4 GR2005 3.70

    My Master Workbook has several columns including P/No and Price
    I need to update the Price in this master Workbook from the saved Sage export data.
    The common data in both workbooks is the P/no.
    I need to have both workbooks open and then push a macro button to
    automaticaly update the cost prices in the Master Workbook.
    Can someone let me have the code for doing this?
    Thanks 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

    Please give some more information...

    Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list?

    When you are requesting code, give as much information as you possibly can. It will save you alot of time.

    If you can edit your own code then disreguard the following:

    Especially important in this case.
    What is the name of your Master Workbook.
    Include the full path.
    What is the name of the sheet where the updates will take place?
    What is the first row of data for P/No
    Which column is P/No located?
    Which column is Price located?

    From what I can gather, you would want the price updated if the P/No already exists.
    If the P/No is new then it would be added to the list and then sorted alphabetically?

    Tom

    [ This Message was edited by: TsTom on 2002-03-24 08:31 ]

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

    Default

    Hi Tom ...


    Thanks for the offer of help. Answers as follows :-

    Please give some more information...

    Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list?

    I only need to update the items that have a
    P/No already, so its just updating existing
    P/No and prices. If the output dump from Sage contains P/Nos not aleady in the Master Workbook then these can be ignored so no append neccessary.


    When you are requesting code, give as much information as you possibly can. It will save you alot of time.

    OK point taken

    If you can edit your own code then disreguard the following:

    I can edit the code , no problem.

    Especially important in this case.
    What is the name of your Master Workbook.

    Dont know yet but lets say c:Mast.xls
    Include the full path.

    Dont know yet but lets say c:Mast.xls

    What is the name of the sheet where the updates will take place?

    Say c:update.xls

    What is the first row of data for P/No

    Say C18

    Which column is P/No located?

    Say C

    Which column is Price located?

    Say E

    From what I can gather, you would want the price updated if the P/No already exists.

    Yes

    If the P/No is new then it would be added to the list and then sorted alphabetically?

    No, it can be ignored.

    Hope this helps Tom

    Rgds Ted


  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

    No problem
    I have guests over.
    Post if you found a solution from someone else. If not, I will send it to you tonight.

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

    Default

    Friend,
    May following code work for you
    Sub assignvalue()

    Dim assign As String
    Application.ScreenUpdating = False

    Workbooks.Open Filename:="c:my documentsCallingFileName.xls"

    Workbooks("CallingFileName").Activate
    Worksheets("sheet1").Select
    assign = Range("a9").Value
    Workbooks("nameofyourbook").Activate
    Worksheets("Sheet1").Select
    Range(b1).Value = assign
    Workbooks("CallingFileName").Close

    Application.ScreenUpdating = True

    End Sub


    If you have difficulty contact me on

    nisht@pexcel.com

    ni****h desai

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

    I emailed the sheets to you.
    If you did not get them, let me know...
    Tom

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

    Default

    Thanks all for your help.
    Ed

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
  •  

 

DMCA.com