Hello All,
I am looking for help to fix a macro, or build a new one.
Goal: To create a looping macro which will copy/paste a column of store numbers into into a single cell in another sheet (on a 1 by 1 basis) and then paste 2 sets of returned results into the original sheet. The two sheets are called 'Summary' and 'Margin Model 1'.
Example:
Sheet 1 named 'Summary' - This is the first sheet. The store # (A2) will be copied into cell A1 in the second sheet. The price and margin data present here comes from the second sheet named 'Margin Model 1'.
<tbody>
</tbody>
Sheet 2 named 'Margin Model 1' - This is the second sheet with the data to be copied into the first sheet. When cell A1 is changed, VLookups retrieve the Price and Margin data.
<tbody>
</tbody>
Existing Code: Very wrong - ignore the cell numbers, very different in real life
Sub Copy_test()
Sheets("Summary").Select
Range("E3:E69").Select
Sheets("Margin Model 1").Select
Range("c1").Select
Do Until ActiveCell = ""
ActiveCell.Copy
Sheets("Summary").Select
Range("a3:a69").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("c1").Select
Range("j6:k6").Copy
Sheets("Summary").Select
Range("c3:d69").Select
Range("j10:k10").Copy
Sheets("Summary").Select
Range("e3:f69").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(3, 0).Select
Sheets("Summary").Select
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Model").Select
Range("A1").Select
End Sub
Any help would be greatly appreciated. Please and thank you.
I am looking for help to fix a macro, or build a new one.
Goal: To create a looping macro which will copy/paste a column of store numbers into into a single cell in another sheet (on a 1 by 1 basis) and then paste 2 sets of returned results into the original sheet. The two sheets are called 'Summary' and 'Margin Model 1'.
Example:
Sheet 1 named 'Summary' - This is the first sheet. The store # (A2) will be copied into cell A1 in the second sheet. The price and margin data present here comes from the second sheet named 'Margin Model 1'.
Store # | Province | 2017 Price | 2018 Price | 2017 Margin | 2018 Margin |
1 | ON | 5 | 7 | 1 | 2 |
2 | AB | ||||
3 | ON | ||||
4 | BC | ||||
5 | ON |
<tbody>
</tbody>
Sheet 2 named 'Margin Model 1' - This is the second sheet with the data to be copied into the first sheet. When cell A1 is changed, VLookups retrieve the Price and Margin data.
1 | 2017 | 2018 | |||
Price | 5 | 7 | |||
Margin | 1 | 2 | |||
<tbody>
</tbody>
Existing Code: Very wrong - ignore the cell numbers, very different in real life
Sub Copy_test()
Sheets("Summary").Select
Range("E3:E69").Select
Sheets("Margin Model 1").Select
Range("c1").Select
Do Until ActiveCell = ""
ActiveCell.Copy
Sheets("Summary").Select
Range("a3:a69").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("c1").Select
Range("j6:k6").Copy
Sheets("Summary").Select
Range("c3:d69").Select
Range("j10:k10").Copy
Sheets("Summary").Select
Range("e3:f69").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(3, 0).Select
Sheets("Summary").Select
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Model").Select
Range("A1").Select
End Sub
Any help would be greatly appreciated. Please and thank you.