nirvehex
04-09-2016, 02:01 PM
Hi All :hi:
I have two worksheets, "Raw Data" and "Data Table". The "Raw Data" tab has data in columns A to H all with the same amount of rows. The "Data Table" tab has a table called "DataTable". I"m trying to use VBA to copy several ranges from the "Raw Data" tab to the "Data Table" tab. Then perform 2 custom formulas at the end.
Here is the step by step of what I'm trying to do:
1. Delete all rows of the "DataTable" on the "Data Table" tab except for the header and row 2.
2. Copy the range A3:E3 to the last row in column E from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell A2 on the "Data Table" tab.
3. Copy the range F3 to the last row in column F from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell H2 on the "Data Table" tab.
4. Copy the range G3 to the last row in column G from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell J2 on the "Data Table" tab.
5. Copy the range H3 to the last row in column H from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell F2 on the "Data Table" tab.
6. On the "Data Table" tab, I would like the formula in G2 to be =(E2-'Raw Data'!$E$2)/'Raw Data'!$E$2. Then in G3 to last row in G, I would like the formula to be =(E3-E2)/E2 and when it gets dropped down the row numbers should adjust.
7. On the "Data Table" tab, I would like the formula in I2 to the last row in I to be =E2-H2 and when it gets dropped down the row numbers should adjust.
8. On the "Data Table" tab, I would like the formula in K2 to be =IF(E2>'Raw Data'!$E$2,1,IF(E2='Raw Data'!$E$2,3,2)). Then in K3 to the last row in K, I would like the formula to be =IF(E3>E2,1,IF(E3=E2,3,2)). Then when it gets dropped down the row numbers should adjust.
9. On the "Data Table" tab, I would like the formula in L2 to the last row in L to be =IF(K2=1,"P",IF(K2=2,"B","F")). Then when it gets dropped down the row numbers should adjust.
I hope this isn't too much to help with. I tried to write it out step by step. I was trying to use the macro recorder to help me, but I'm having trouble following it and converting individual cell references to ranges.
I've attached a test file in case it is helpful.
Thanks All!
I have two worksheets, "Raw Data" and "Data Table". The "Raw Data" tab has data in columns A to H all with the same amount of rows. The "Data Table" tab has a table called "DataTable". I"m trying to use VBA to copy several ranges from the "Raw Data" tab to the "Data Table" tab. Then perform 2 custom formulas at the end.
Here is the step by step of what I'm trying to do:
1. Delete all rows of the "DataTable" on the "Data Table" tab except for the header and row 2.
2. Copy the range A3:E3 to the last row in column E from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell A2 on the "Data Table" tab.
3. Copy the range F3 to the last row in column F from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell H2 on the "Data Table" tab.
4. Copy the range G3 to the last row in column G from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell J2 on the "Data Table" tab.
5. Copy the range H3 to the last row in column H from the "Raw Data" tab to the "Data Table" tab. I would like it to paste, starting in cell F2 on the "Data Table" tab.
6. On the "Data Table" tab, I would like the formula in G2 to be =(E2-'Raw Data'!$E$2)/'Raw Data'!$E$2. Then in G3 to last row in G, I would like the formula to be =(E3-E2)/E2 and when it gets dropped down the row numbers should adjust.
7. On the "Data Table" tab, I would like the formula in I2 to the last row in I to be =E2-H2 and when it gets dropped down the row numbers should adjust.
8. On the "Data Table" tab, I would like the formula in K2 to be =IF(E2>'Raw Data'!$E$2,1,IF(E2='Raw Data'!$E$2,3,2)). Then in K3 to the last row in K, I would like the formula to be =IF(E3>E2,1,IF(E3=E2,3,2)). Then when it gets dropped down the row numbers should adjust.
9. On the "Data Table" tab, I would like the formula in L2 to the last row in L to be =IF(K2=1,"P",IF(K2=2,"B","F")). Then when it gets dropped down the row numbers should adjust.
I hope this isn't too much to help with. I tried to write it out step by step. I was trying to use the macro recorder to help me, but I'm having trouble following it and converting individual cell references to ranges.
I've attached a test file in case it is helpful.
Thanks All!