Solved Using a macro to update a waterfall chart

April 5, 2012 at 11:29:33
Specs: Windows 7
I have a spreadsheet that I want to automate updating. I recieve a report (in excel format) that contains update data daily. I want to make a macro that looks up a serial number on the update sheet and pastes the updated info into an appropriate cell on the master sheet. Specifically, the report is for manufacturing an item, I get in the report the Serial Number, the last operation performed, and the timestamp of that operation. I need to then put the timestamp into the appropriate operation field as defined by the report. (so the report tells me SN 1 had operation 1000 performed on 1/1. I then input the date 1/1 into the Op 1000 column of the SN1 Row. On 1/2 the report says SN 1 had operation 1001 performed on it, so I put 1/2 in the op 1001 column of SN1 row. ) I want to build a macro to automate this.

See More: Using a macro to update a waterfall chart

Report •


✔ Best Answer
April 11, 2012 at 18:51:09
What's the point of the VLOOKUP if the macro is going to determine where to place the date? Why can't the macro just use the Last Op data to determine the correct column?

If I expand the example you posted in Response # 8, I assume you want something like this:

      A        B         C            D         E         F   ...    S	
1   Sn      Last Op  Timestamp      Op 1      Op 2      Op 3  ...  Op 15
2  x101       3      04/01/12                         04/01/12			
3  x102       1      04/02/12     04/02/12		
4  x103      15      04/03/12                                    04/03/12	
5  x104       2      04/04/12               04/04/12

You can create that table with this:

Sub TimeStamper()
Dim lastRw, rw As Integer
'Determine last row with S/N data
  lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
   For rw = 2 To lastRw
'Copy Column C to correct Ops Column based
'on value in Column B + 3 Columns
    Cells(rw, Cells(rw, 2) + 3) = Cells(rw, 3)
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 6, 2012 at 00:25:58
What you are asking for can probably be done but we need a lot more information about the source and destination workbooks before we could offer any specific code.

Since you need to tell VBA where to get the data from and where to put it, we would need to know that type of information.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 9, 2012 at 11:31:09
Ok, so I've simplified my process some, using some Vlookups to generate my target cell. All i need to do now is set up a macro that steps through a sheet row by row, and copies data from one cell and pastes it in to a designated target cell . A simplified example below:
I need to copy the Date contained in the row 1 date cell and paste it into the cell designated in the row 1 target cell. Then move on to row 2, etc, and terminate when the SN field is empty.

 SN Date Target
1 1 4/1 F8
2 2 4/1 f9
3 3 4/2 g10


Report •

#3
April 9, 2012 at 12:10:58
Why not just use a formula?

=A1, etc.

Why do you need a macro to have the same value appear in 2 different cells?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 9, 2012 at 12:16:05
because the daily reports I get change, I need to put the daily info into a stable datasheet. For example, today the data in line one would say "sn 1, 4/1, f8" whereas tomorrow it might say "Sn1, 4/2, g8".. if it were static data i wouldn't need to, but as the backend data changes daily, I need to import it.

Report •

#5
April 9, 2012 at 18:08:42
How are you importing the data?

If the daily report was saved as a .csv file (or similar) you could set up a query and just refresh it each time. Since only the query fields (cells) would get updated, the cells with formula e.g. =A1 would reflect the new data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 10, 2012 at 06:02:38
the import file currently is an excel file,.XLSX, and I have links set up so I can just overwrite the old file and the data fills in, but the problem I have is the report will contain the following data:

Serial Number     Current Operation     Current Time Stamp

x10001               1000                        4/10/2012
x10002               2800                        4/10/2012

and tomorrows report would say

Serial Number     Current Operation     Current Time Stamp

x10001               1200                        4/11/2012
x10002               3000                        4/11/2012

so I can't just have the system copy the data cold. the Operation number is what sets what cell the current time stamp needs to be placed in.


Report •

#7
April 10, 2012 at 08:04:07
From your example, I don't see any information that shows how the "Current Time Stamp" cell location is determined.

From what I can tell you actually need something like this:

SN = Serial Number
CO = Current Operation
CTS = Current Time Stamp


     A      B         C          D        E
1    SN     CO       CTS        CTS      CTS
2  x10001  1200    4/11/2012
3  x10002  3000                        4/11/2012
4  x1003   4000               4/11/2012

If that's the case, then there must be some criteria that detemines which Current Time Stamp cell to use for each piece of Current Operation data. Unless we know that criteria, we can't offer any code or formula suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 10, 2012 at 09:03:24
sorry, i wasn't being clear. the current operation is what sets the location.
my actual working sheet is set up as follows:
Sn    Last Operation     Timestamp     Operation 1     operation 2     operation 3
x101  3                  4/7/12        4/3/12          4/5/12            

The Last Operation and Timestamp fields are pulled from the update report with a Vlookup on the serial number. I just want a macro that looks at the Last Operation cell, and then pastes the data in timestamp into the cell on the right that corresponds to the last operation. so in this example, because the last operation is 3 and there is currently no data in the Operation 3 cell, it should copy the data from the Timestamp field and paste it in the operation 3 cell.
I'm sorry I have to be so frightfully vague with my examples, its what comes from working for a defense contractor.


Report •

#9
April 10, 2012 at 11:53:00
re: "so in this example, because the last operation is 3 and there is currently no data in the Operation 3 cell, it should copy the data from the Timestamp field and paste it in the operation 3 cell."

You've listed 2 criteria to determine where the time stamp should be placed:

"...the last operation is 3" and "there is currently no data in the Operation 3 cell"

What if there was data in the Operation 3 cell, or if the Last Operation was 2?

Would you overwrite existing data?

One other thing that is making this extremely difficult to understand:

In one post you used the term "target". In another, you used "Current Time Stamp" and in yet another you used "operation 3".

If the code needs to determine what cell to place the time stamp in, it is going to need something to search for based on the data in the Last Operation cell(or is it Current Operation - you've changed that a number of times also)

Unless we know the exact terms you will be using or the exact columns for each operation, we can't offer anything that will do you any good.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
April 11, 2012 at 05:38:44
ok, my Columns are as follows

S/N: this is the serial number for the parts.

Last Op:- This cell is automatically filled with the information from my daily report, and tells me what operation was last run on the part, could be any number between 1 and 15.

Last Op Timestamp: This cell is automatically filled from the Daily report, tells me what date the Last Operation listed in the Last Op cell occurred

Start Date: the date the part started moving through operations

Operation 1: what date the part went through operation 1

Operation 2: what date the part went through operation 2,

ETC... out to
Operation 15: what date teh part went through operation 15.

Each Row is a new serial number.
if there is a date already in the operation cell it should be overwritten, as it is possible for a part to be logged going through an operation more than once, and the last time through is the time we need to record

What I have currently is a lookup against a table, looks up the "last op" and returns what column that corresponds to, so all I really need is a macro that looks at the data in cell a, (which contains a cell reference) copy the data in cell b, and paste it into the cell reference contained in cell A.


Report •

#11
April 11, 2012 at 18:51:09
✔ Best Answer
What's the point of the VLOOKUP if the macro is going to determine where to place the date? Why can't the macro just use the Last Op data to determine the correct column?

If I expand the example you posted in Response # 8, I assume you want something like this:

      A        B         C            D         E         F   ...    S	
1   Sn      Last Op  Timestamp      Op 1      Op 2      Op 3  ...  Op 15
2  x101       3      04/01/12                         04/01/12			
3  x102       1      04/02/12     04/02/12		
4  x103      15      04/03/12                                    04/03/12	
5  x104       2      04/04/12               04/04/12

You can create that table with this:

Sub TimeStamper()
Dim lastRw, rw As Integer
'Determine last row with S/N data
  lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
   For rw = 2 To lastRw
'Copy Column C to correct Ops Column based
'on value in Column B + 3 Columns
    Cells(rw, Cells(rw, 2) + 3) = Cells(rw, 3)
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
April 12, 2012 at 09:53:27
Thank you, that looks to do exactly what I'm looking for, thank you!

Report •

Ask Question