Macro to Update Data Between 2 Workbooks

Soprano82

New Member
Joined
Feb 8, 2014
Messages
2
Hey guys,

I am having some issues creating a macro to update cell data in a "master" workbook from another workbook that is generated weekly based on a Part ID. The Part ID is located in Column A in both workbooks, and the weights in the workbook to be read are in Column B. Here's a sample to give you an idea of what the "master" sheet looks like:

A B C D,E,F G
(Row 1) Part Number Secondary ID Description (3 other Columns here) Latest Weight
X0054 ..... ..... ..... Column to be updated.
X0055
Column to be Identified.

As a secondary function, I need the macro to copy and paste the previous "Latest Weight" to the next empty column to right of the latest weight column, keeping the data starting in row 2, (so that the previous week's weight data will be in Column H when the macro is run this week, and in a week from now, when the new sheet is made, this week's data will be in Column I, and so on) before the macro updates the "Latest Weight" column with the new data obviously. I have to maintain the old weight data to keep a running average and standard deviation of the weights. Column D will be average, Column E will be standard deviation, and Column F will be delta, that is the difference between the updated weight data and most previous weight data. Finally, I have not been able to wrap my mind around how to automate the delta calculation, as the most previous weight data location will clearly change after the macro has run each time. I believe the average and standard deviation calculations will be fairly straightforward with the count function though. I have looked up this question on many forums, but nothing I have found seems adequate for what I am looking for. I have very little knowledge of VBA, so any assistance would be much appreciated.

Thank you.
 

Soprano82

New Member
Joined
Feb 8, 2014
Messages
2
Sorry to double post, but the formatting I had setup got lost when I posted this. Here's something that is a little more clear.

Hey guys,

I am having some issues creating a macro to update cell data in a "master" workbook from another workbook that is generated weekly based on a Part ID. The Part ID is located in Column A in both workbooks, and the weights in the workbook to be read are in Column B. The "master" spreadsheet is set up as follows: Column A is the Part ID (Column to be Identified), Column B is a secondary ID, Column C is Description, Column D is Average, Column E is Standard Deviation, Column F is Delta, that is the difference between the updated weight data and most previous weight data, and Column G is the Latest Weight Column (Column to be Updated). All data is input starting in the 2nd row running downward under each heading.

As a secondary function, I need the macro to copy and paste the previous "Latest Weight" to the next empty column to right of the latest weight column, keeping the data starting in row 2, (so that the previous week's weight data will be in Column H when the macro is run this week, and in a week from now, when the new sheet is made, this week's data will be in Column I, and so on) before the macro updates the "Latest Weight" column with the new data obviously. I have to maintain the old weight data to keep a running average and standard deviation of the weights. Finally, I have not been able to wrap my mind around how to automate the average, standard deviation, and delta calculation as the the additional column of data is added each time after the macro runs. I have looked up this question on many forums, but nothing I have found seems adequate for what I am looking for. I have very little knowledge of VBA, so any assistance would be much appreciated.

Thank you.
 

Forum statistics

Threads
1,082,283
Messages
5,364,273
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top