PDA

View Full Version : [SOLVED] Copy and Paste Data from One Tab to Another



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!

Paul_Hossler
04-09-2016, 03:33 PM
You can play with this. I think it what you asked for, but double check




Option Explicit
Sub DoStuff()
Dim wsRaw As Worksheet, wsData As Worksheet
Dim rRaw As Range, rData As Range, rCell As Range

'start setup
Set wsRaw = Worksheets("Raw Data")
Set wsData = Worksheets("Data Table")

Set rRaw = wsRaw.Cells(1, 1).CurrentRegion
Set rData = wsData.Cells(1, 1).CurrentRegion


'1. Delete all rows of the "DataTable" on the "Data Table" tab except for the header and row 2
Set rCell = rData.Cells(3, 1)
With rCell
Range(rCell, rCell.End(xlDown).End(xlToRight)).ClearContents
End With

'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.
Range(wsRaw.Range("A3"), wsRaw.Range("A3").End(xlDown)).Resize(, 5).Copy wsData.Range("A2")


'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.
Range(wsRaw.Range("F3"), wsRaw.Range("F3").End(xlDown)).Copy wsData.Range("H2")


'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.
Range(wsRaw.Range("G3"), wsRaw.Range("G3").End(xlDown)).Copy wsData.Range("J2")


'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.
Range(wsRaw.Range("H3"), wsRaw.Range("H3").End(xlDown)).Copy wsData.Range("F2")


'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.
wsData.Range("G2").Formula = "=(E2-'Raw Data'!$E$2)/'Raw Data'!$E$2"
Range(wsData.Range("G3"), wsData.Range("G3").End(xlDown)).Formula = "=(E3-E2)/E2"


'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.
Range(wsData.Range("I2"), wsData.Range("I2").End(xlDown)).Formula = "=E2-H2"


'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.
wsData.Range("K2").Formula = "=IF(E2>'Raw Data'!$E$2,1,IF(E2='Raw Data'!$E$2,3,2))"
Range(wsData.Range("K3"), wsData.Range("K3").End(xlDown)).Formula = "=IF(E3>E2,1,IF(E3=E2,3,2))"


'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.
Range(wsData.Range("L2"), wsData.Range("L2").End(xlDown)).Formula = "=IF(K2=1,""P"",IF(K2=2,""B"",""F""))"
End Sub

nirvehex
04-11-2016, 05:36 PM
Paul,

Thank you that worked perfectly as do all your codes that you've helped me with! Hey I was wondering--I see you are on the east coast, I'm in CT, you're in PA--do you know of any reputable places to get professionally trained in Excel based VBA? Any organizations you would recommend? My boss has hinted that he might have some money for me in the budget to get some training on this topic. Let me know if you have any recommendations.

Thanks again!