Copy pasting templates while changing values from the control panel [VBA]

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hello, can anyone please help me to work on a copy pasting work with a VBA Code,

I am attaching an excel file with some data and 2 templates, to explain what I need,

One sheet is the "Control Panel" where I want to setup how I need to arrange those 2 templates,

In the templates I need to change few values and then copy paste to the desired number of times, elements need to be changed are highlighted with Yellow color in the templates, and the desired values are in "Control Panel" sheet,

If I take an example of the "Template 1", I have 20 kws total with 10 kws/campaign, for 20 kws total 2 campaigns/sku, for 2 skus, total 4 campaigns,

For SKU 1: only difference in the 2 campaigns will be the kws and campaign names,

Between the campaigns of SKU 1 and SKU 2, only difference is the SKU and campaign names while kws are same,

I hope it makes sense, also to make it more understandable, I have made a demo for this to show how it is going to look after the code will run, and it has their own separate colors which correlates to the origin value in control panel so you know which value is coming from where,

Thank you!
Templates
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Template 1
2Sponsored ProductsCampaignCreateSP - KeywordSP - Keyword20220207MANUALenabled100Dynamic bids - down only
3Sponsored ProductsAd GroupCreateSP - KeywordSP - Keyword - GroupSP - Keyword - Groupenabled0.5
4Sponsored ProductsProduct AdCreateSP - KeywordSP - Keyword - GroupenabledSKU 1
5Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 1Exact
6Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 2Exact
7Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 3Exact
8Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 4Exact
9Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 5Exact
10Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 6Exact
11Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 7Exact
12Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 8Exact
13Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 9Exact
14Sponsored ProductsKeywordCreateSP - KeywordSP - Keyword - Groupenabled0.5keyword 10Exact
15
16Template 2
17Sponsored ProductsCampaignCreateSP - ASIN TargetingSP - ASIN Targeting20220207MANUALenabled100Dynamic bids - down only
18Sponsored ProductsAd GroupCreateSP - ASIN TargetingSP - ASIN Targeting - GroupSP - ASIN Targeting - Groupenabled0.5
19Sponsored ProductsProduct AdCreateSP - ASIN TargetingSP - ASIN Targeting - GroupenabledSKU 1
20Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 1
21Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 2
22Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 3
23Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 4
24Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 5
25Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 6
26Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 7
27Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 8
28Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 9
29Sponsored ProductsProduct TargetingCreateSP - ASIN TargetingSP - ASIN Targeting - Groupenabled0.5ASIN 10
Templates

Control Panel
Book1
ABCDEFGHIJKLMNOPQRSTUVW
1Template 1Template 2
2Copy template for this many times3Campaigns3
3Total SKUs2Total SKUs2
4Total Campaigns for all SKUs6Total Campaigns6
5Keyword 11234Start Date20220515Campaign 1 Name"Campaign 1" + SKUSKU 14567ASIN 15678Start Date20220515Campaign 1 Name"Campaign 1" + SKUSKU 14567
6Keyword 21235Bid0.5Campaign 2 Name"Campaign 2" + SKUSKU 24568ASIN 25679Bid0.4Campaign 2 Name"Campaign 2" + SKUSKU 24568
7Keyword 31236ASIN 35680
8Keyword 41237ASIN 45681
9Keyword 51238ASIN 55682
10Keyword 61239ASIN 65683
11Keyword 71240ASIN 75684
12Keyword 81241ASIN 85685
13Keyword 91242ASIN 95686
14Keyword 101243ASIN 105687
15Keyword 111244ASIN 115678
16Keyword 121245ASIN 125679
17Keyword 131246ASIN 135680
18Keyword 141247ASIN 145681
19Keyword 151248ASIN 155682
20Keyword 161249ASIN 165683
21Keyword 171250ASIN 175684
22Keyword 181251ASIN 185685
23Keyword 191252ASIN 195686
24Keyword 201253ASIN 205687
Control Panel

Demo
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1ProductEntityOperationCampaign IdAd Group IdPortfolio IdAd IdKeyword IdProduct Targeting IdCampaign NameAd Group NameStart DateEnd DateTargeting TypeStateDaily BudgetSKUASINAd Group Default BidBidKeyword TextMatch TypeBidding StrategyPlacementPercentageProduct Targeting Expression
2Sponsored ProductsCampaignCreateCampaign 1 4567Campaign 1 456720220515MANUALenabled100Dynamic bids - down only
3Sponsored ProductsAd GroupCreateCampaign 1 4567Campaign 1 4567Campaign 1 4567enabled0.5
4Sponsored ProductsProduct AdCreateCampaign 1 4567Campaign 1 4567enabled4567
5Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51234Exact
6Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51235Exact
7Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51236Exact
8Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51237Exact
9Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51238Exact
10Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51239Exact
11Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51240Exact
12Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51241Exact
13Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51242Exact
14Sponsored ProductsKeywordCreateCampaign 1 4567Campaign 1 4567enabled0.51243Exact
15
16Sponsored ProductsCampaignCreateCampaign 2 4567Campaign 2 456720220515MANUALenabled100Dynamic bids - down only
17Sponsored ProductsAd GroupCreateCampaign 2 4567Campaign 2 4567Campaign 2 4567enabled0.5
18Sponsored ProductsProduct AdCreateCampaign 2 4567Campaign 2 4567enabled4567
19Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51244Exact
20Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51245Exact
21Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51246Exact
22Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51247Exact
23Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51248Exact
24Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51249Exact
25Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51250Exact
26Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51251Exact
27Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51252Exact
28Sponsored ProductsKeywordCreateCampaign 2 4567Campaign 2 4567enabled0.51253Exact
29
30Sponsored ProductsCampaignCreateCampaign 1 4568Campaign 1 456820220515MANUALenabled100Dynamic bids - down only
31Sponsored ProductsAd GroupCreateCampaign 1 4568Campaign 1 4568Campaign 1 4568enabled0.5
32Sponsored ProductsProduct AdCreateCampaign 1 4568Campaign 1 4568enabled4568
33Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51234Exact
34Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51235Exact
35Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51236Exact
36Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51237Exact
37Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51238Exact
38Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51239Exact
39Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51240Exact
40Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51241Exact
41Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51242Exact
42Sponsored ProductsKeywordCreateCampaign 1 4568Campaign 1 4568enabled0.51243Exact
43
44Sponsored ProductsCampaignCreateCampaign 2 4568Campaign 2 456820220515MANUALenabled100Dynamic bids - down only
45Sponsored ProductsAd GroupCreateCampaign 2 4568Campaign 2 4568Campaign 2 4568enabled0.5
46Sponsored ProductsProduct AdCreateCampaign 2 4568Campaign 2 4568enabled4568
47Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51244Exact
48Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51245Exact
49Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51246Exact
50Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51247Exact
51Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51248Exact
52Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51249Exact
53Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51250Exact
54Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51251Exact
55Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51252Exact
56Sponsored ProductsKeywordCreateCampaign 2 4568Campaign 2 4568enabled0.51253Exact
57
58Sponsored ProductsCampaignCreateCampaign 1 4567Campaign 1 456720220515MANUALenabled100Dynamic bids - down only
59Sponsored ProductsAd GroupCreateCampaign 1 4567Campaign 1 4567Campaign 1 4567enabled0.4
60Sponsored ProductsProduct AdCreateCampaign 1 4567Campaign 1 4567enabled4567
61Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45678
62Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45679
63Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45680
64Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45681
65Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45682
66Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45683
67Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45684
68Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45685
69Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45686
70Sponsored ProductsProduct TargetingCreateCampaign 1 4567Campaign 1 4567enabled0.45687
71
72Sponsored ProductsCampaignCreateCampaign 2 4567Campaign 2 456720220515MANUALenabled100Dynamic bids - down only
73Sponsored ProductsAd GroupCreateCampaign 2 4567Campaign 2 4567Campaign 2 4567enabled0.4
74Sponsored ProductsProduct AdCreateCampaign 2 4567Campaign 2 4567enabled4567
75Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45678
76Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45679
77Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45680
78Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45681
79Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45682
80Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45683
81Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45684
82Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45685
83Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45686
84Sponsored ProductsProduct TargetingCreateCampaign 2 4567Campaign 2 4567enabled0.45687
85
86Sponsored ProductsCampaignCreateCampaign 1 4568Campaign 1 456820220515MANUALenabled100Dynamic bids - down only
87Sponsored ProductsAd GroupCreateCampaign 1 4568Campaign 1 4568Campaign 1 4568enabled0.4
88Sponsored ProductsProduct AdCreateCampaign 1 4568Campaign 1 4568enabled4568
89Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45678
90Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45679
91Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45680
92Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45681
93Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45682
94Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45683
95Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45684
96Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45685
97Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45686
98Sponsored ProductsProduct TargetingCreateCampaign 1 4568Campaign 1 4568enabled0.45687
99
100Sponsored ProductsCampaignCreateCampaign 2 4568Campaign 2 456820220515MANUALenabled100Dynamic bids - down only
101Sponsored ProductsAd GroupCreateCampaign 2 4568Campaign 2 4568Campaign 2 4568enabled0.4
102Sponsored ProductsProduct AdCreateCampaign 2 4568Campaign 2 4568enabled4568
103Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45678
104Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45679
105Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45680
106Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45681
107Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45682
108Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45683
109Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45684
110Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45685
111Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45686
112Sponsored ProductsProduct TargetingCreateCampaign 2 4568Campaign 2 4568enabled0.45687
Demo
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top