Data Update (From one workbook to another)

[eddy]

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

 

[Legacy 98055]

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

 

[eddy]

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

 

[Legacy 98055]

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

 

[nisht]

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

 

[Legacy 98055]

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

 

[eddy]

Thanks all for your help.
Ed