VBA code to repeat item labels in a column

[Wafee]

Hi, Can you somone help me with this request.

I have data as shown in column A and i need it to be done as in column C for some calculation and once it is done it has be restored as in Column A again. this process needs to be performed in column "A" itself. Consider sheet name as sheet1 and activeworkbook.

It is similar to what is done when we select repeat item lables in a pivot. it has to be done until "Grand total" cell. Thank you in advance.

Book1
	A	B	C
1	Name		Name
2
3
4	Raju		Raju
5			Raju
6			Raju
7			Raju
8			Raju
9			Raju
10	Hari		Hari
11			Hari
12	Ram		Ram
13			Ram
14			Ram
15	Moorthy		Moorthy
16			Moorthy
17			Moorthy
18			Moorthy
19	Grand total
Sheet1

 

[Fluff]

How about

Sub wafee()
   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("*", , , , xlByRows, xlNext, , , False)
   With Range(Fnd, Range("A" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[Wafee]

How about

Sub wafee()
   Dim Fnd As Range
  
   Set Fnd = Range("A:A").Find("*", , , , xlByRows, xlNext, , , False)
   With Range(Fnd, Range("A" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

Thank you Fluff, Can you also help me with bit of code that restores intial data. it has to retain first occurance of every name and delete rest.

 

[Fluff]

Why would you want to remove the data you have just added?

 

[Wafee]

Why would you want to remove the data you have just added?

I need these rows filled in order to simplify the formula that I am gonna use. Once I obtain values for the formulas I am planning to value paste them and bring column "A" back to as it is as that should be the final view.

 

[Fluff]

Must admit I don't understand why, it makes absolutely no sense to me.
That said use

Sub wafee()
   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("*", , , , xlByRows, xlNext, , , False)
   With Range(Fnd, Range("A" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   End With
End Sub

and

Sub wafee2()
Range("A:A").SpecialCells(xlFormulas).ClearContents
End Sub

 

[Wafee]

Must admit I don't understand why, it makes absolutely no sense to me.
That said use

Sub wafee()
   Dim Fnd As Range
  
   Set Fnd = Range("A:A").Find("*", , , , xlByRows, xlNext, , , False)
   With Range(Fnd, Range("A" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   End With
End Sub

and

Sub wafee2()
Range("A:A").SpecialCells(xlFormulas).ClearContents
End Sub

Hi Fluff, Let me expain why I am trying to do this if you don't mind and help me if with any better way if you can.

I have beow pivot table as my source. here i have selected repeat item labels option. Column A has senior Manager and Column "B" has managers under the senior manager.

Copy of Overall Shankar_Cox_UnapprovedTime Report.xlsm
	A	B																																								AP
1	Sum of DURATION
2	Senior Manager	Manager																																								Grand Total
3	DINGARI, HRUSHIKESH K																																									2103.72
4	DINGARI, HRUSHIKESH K	NAIR, GIRISH																																								1143.72
5	DINGARI, HRUSHIKESH K	SARKAR, ASHIS																																								720
6	DINGARI, HRUSHIKESH K	(blank)																																								240
7	FAHEEM, WAEL																																									5675.5
8	FAHEEM, WAEL	AGARWAL, DINESH																																								1300.5
9	FAHEEM, WAEL	CHANDRASEKHARAN, RAJEEV																																								3686.5
10	FAHEEM, WAEL	CHANG, SOK WU W																																								90
11	FAHEEM, WAEL	WHITE, CHRIS L																																								500.5
12	FAHEEM, WAEL	(blank)																																								98
13	GERMANOTTA, DOMINIC																																									3632.25
14	GERMANOTTA, DOMINIC	BONOMO, MICHAEL																																								1092.75
15	GERMANOTTA, DOMINIC	HALL, TAMARA L																																								160
16	GERMANOTTA, DOMINIC	Wafee																																								40
17	GERMANOTTA, DOMINIC	RANGARAJAN, JAGANNATH K																																								1751
18	GERMANOTTA, DOMINIC	STRATFORD, CHRIS																																								547.5
19	GERMANOTTA, DOMINIC	STRATTON, LAURA M																																								41
20	GURUMURTHY, VIVEK																																									18041.8
21	GURUMURTHY, VIVEK	BASU, SANJAY																																								458.83
22	GURUMURTHY, VIVEK	BELIHOMJI, ADIL																																								9802.5
23	GURUMURTHY, VIVEK	JAIN, ARCHANA																																								6365.97
24	GURUMURTHY, VIVEK	NIRANJANI, HIMANSHU																																								1324
25	GURUMURTHY, VIVEK	SUDHARSAN, VELAMUR S																																								90.5
26	MADERA, ISAAC A																																									4863.99
27	MADERA, ISAAC A	BAESSLER, CHRISTINA																																								2719.49
28	MADERA, ISAAC A	CASO, ANDREW T																																								76
29	MADERA, ISAAC A	GREGORY, LISA M																																								709
30	MADERA, ISAAC A	JOBERT, SEBASTIEN																																								635
31	MADERA, ISAAC A	KERCHER, TODD E																																								40
32	MADERA, ISAAC A	PAONE, KELLY M																																								246
33	MADERA, ISAAC A	REPER, LAURA L																																								358.5
34	MADERA, ISAAC A	VENKATRAMAN, SRINIVAS																																								80
35	RAMANATHAN, SANKARAN																																									1249
36	RAMANATHAN, SANKARAN	DAMIANO, NATALIE M																																								291.5
37	RAMANATHAN, SANKARAN	JOSHI, ANIRUDHA A																																								339
38	RAMANATHAN, SANKARAN	RAY, ANDREW L																																								498.5
39	RAMANATHAN, SANKARAN	(blank)																																								120
40	SINGH, SUMIT																																									2410.25
41	SINGH, SUMIT	FUJINAMI, GARY G																																								223
42	SINGH, SUMIT	KRIER, RYAN																																								210.5
43	SINGH, SUMIT	PAFF, MARK D																																								80
44	SINGH, SUMIT	SCHANEL, BARBARA J																																								524
45	SINGH, SUMIT	SHARMA, RAJU																																								70
46	SINGH, SUMIT	TANUKU, NAGESWARA RAO																																								900
47	SINGH, SUMIT	(blank)																																								402.75
48	SIVAGANESH, SIVAGNANALINGAM																																									423
49	SIVAGANESH, SIVAGNANALINGAM	KAUL, ASHUMA S																																								120
50	SIVAGANESH, SIVAGNANALINGAM	MYLAVARUPU, VENKAT S																																								183
51	SIVAGANESH, SIVAGNANALINGAM	THAKER, DHAVAL V																																								80
52	SIVAGANESH, SIVAGNANALINGAM	(blank)																																								40
53	SLY, DAVID G																																									1652.5
54	SLY, DAVID G	BARNAK, ROBERT W																																								124
55	SLY, DAVID G	BILLINGER-JONES, ANTOINETTE M																																								321
56	SLY, DAVID G	FONTANA, OTTAVIO S																																								327
57	SLY, DAVID G	HAVEWALA, ASPI																																								191.5
58	SLY, DAVID G	MATEO, ALFRED A																																								523
59	SLY, DAVID G	(blank)																																								166
60	ZIAEE, KAMRAN																																									5787.5
61	ZIAEE, KAMRAN	CHAKRAVARTY, SHOMA																																								743
62	ZIAEE, KAMRAN	KUMAR, NANDA																																								3307
63	ZIAEE, KAMRAN	PASTOR, RICHARD																																								51
64	ZIAEE, KAMRAN	SEKAR, KALYANI																																								392
65	ZIAEE, KAMRAN	(blank)																																								1294.5
66	Sundar																																									40
67	Sundar	Max																																								40
68	(blank)																																									4866.88
69	(blank)	(blank)																																								4866.88
70	Grand Total																																									50746.39
Pivot

Below is the output sheet where I need the update the grand total every week in to a new column.

Copy of Overall Shankar_Cox_UnapprovedTime Report.xlsm
	A	B								J	K	L	M
1
2										YTD to OCT 10	YTD to OCT 17	YTD to OCT 24	YTD to OCT 31
3	Senior Manager/Manager
4	GERMANOTTA, DOMINIC									7747.33	3204.5	3592.25
5		BONOMO, MICHAEL								1697	829.5	1092.75
6		HALL, TAMARA L								1306.33	295.5	160
7		PRADHAN, JEETENDRA								1243.5	279	NA
8		RANGARAJAN, JAGANNATH K								2760.5	1675.5	1751
9		STRATFORD, CHRIS								740	85	547.5
10		STRATTON, LAURA M								NA	40	41
11		((blank)								NA	NA	NA
12	DINGARI, HRUSHIKESH K									6955	3494	2103.72
13		NAIR, GIRISH								2141.5	2829.5	1143.72
14		SARKAR, ASHIS								1842	584.5	720
15	VENKATRAMAN, SRINIVAS									1654	NA
16		HUGHES, GEORGE L								246	NA	NA
17	THAKER, DHAVAL V									942.5
18										129	80	240
19	FAHEEM, WAEL									6901.43	5039.5	5675.5
20		AGARWAL, DINESH								2803	1670.5	1300.5
21		CHANDRASEKHARAN, RAJEEV								2876.34	2740	3686.5
22		CHANG, SOK WU W								40	40	90
23		ZAVAR, MASTANEH								228	NA	NA
24		WHITE, CHRIS L								902.09	589	500.5
25		(blank)								52	NA	98
26	GURUMURTHY, VIVEK									24709.61	20295	18041.8
27		BASU, SANJAY								838.25	780.58	458.83
28		BELIHOMJI, ADIL								12993.44	12266.5	9802.5
29		JAIN, ARCHANA								8242.42	5193.17	6365.97
30		SUDHARSAN, VELAMUR S								428.5	450.5	90.5
31		NIRANJANI, HIMANSHU								2207	1604.25	1324
32		(blank)								NA	NA	NA
33	MADERA, ISAAC A									5674.68	3484.94	4863.99
34		BAESSLER, CHRISTINA								2199.68	1883.94	2719.49
35		CASO, ANDREW T								340	40	76
36		GREGORY, LISA M								290	734	709
37		JOBERT, SEBASTIEN								1363	170	635
38		REPER, LAURA L								716	64	358.5
39		PAONE, KELLY M								216	421	246
40		KERCHER, TODD E								294	40	40
41		VENKATRAMAN, SRINIVAS								NA	NA	80
42		(blank)								256	132	NA
43	MUSE, ERIC V									120	0	0
44		BOWEN, TROY L								120	NA	NA
45		PETTIT, STEPHEN								NA	NA	NA
46		SANKARANARAYANAN, RAMESH								NA	NA	NA
47		TOTH, RICHARD A								NA	NA	NA
48		(blank)								NA	NA	NA
49	RAMANATHAN, SANKARAN									3078.67	1902.16	1249
50		DAMIANO, NATALIE M								583.17	NA	291.5
51		RAY, ANDREW L
52	MYLAVARUPU, VENKAT S									1562
53		RAY, ANDREW L								813.5	602.66	498.5
54		KUSHWAHA, ABHITABH								NA	NA	NA
55		JOSHI, ANIRUDHA A									1139.5	339
56		(blank)								120	160	120
57	SINGH, SUMIT									5155.4	1762.33	2410.25
58		FUJINAMI, GARY G								552	174	223
59		KRIER, RYAN								394.4	120	210.5
60		PAFF, MARK D								966.5	120	80
61		SCHANEL, BARBARA J								561	32	524
62		TANUKU, NAGESWARA RAO								1266.5	821	900
63		KIRKWOOD, SARA M								397	NA	NA
64		SHARMA, RAJU								NA	NA	70
65		(blank)								1018	495.33	402.75
66	SLY, DAVID G									5230.75	1414.73	1652.5
67		BARNAK, ROBERT W								836	44	124
68		BILLINGER-JONES, ANTOINETTE M								1054.5	40	321
69		FONTANA, OTTAVIO S								431	787	327
70		HAVEWALA, ASPI								1001.5	120	191.5
71		MATEO, ALFRED A								1711.25	383.73	523
72		(blank)								196.5	40	166
73	ZIAEE, KAMRAN									6,160.42	5,639.83	5,787.50
74		CHAKRAVARTY, SHOMA								422	340	743
75		KUMAR, NANDA								2905	3259.5	3307
76		MONDRY, MARC W								NA	NA	NA
77		PASTOR, RICHARD								1258.42	252.83	51
78		SEKAR, KALYANI								618.5	672.5	392
79		(blank)								956.5	1115	1294.5
80	SIVAGANESH, SIVAGNANALINGAM									610	365	423
81		JOSHI, ANIRUDHA A								445
82		KAUL, ASHUMA S								125	205	120
83		THAKER, DHAVAL V
84		MYLAVARUPU, VENKAT S
85	SHARMA, RAJU									40	NA
86		THAKER, DHAVAL V									120	80
87		MYLAVARUPU, VENKAT S									40	183
88		(blank)								NA	NA	40
89	Grand Total									72343.29	46601.99	45799.51
View 1 Week on Week Trend - Un

steps.

1.need to compare output(Sheet2) sheet and pivot sheet(sheet1) and check whether any new senior manager is added to the list and if yes then that needs to be added in to the column "A" of sheet to above Grand total. if any senior manager is removed nothing to be done. it should also ignore (blanks) in senior manager column.

2. Need to copare managers under senior managers and if there is an addition or movement (Managers might move from one senior managers to other) then that manager name to be added under respective senior manager. In case of movement we alos need to retain existing line as it might contain data for past week. it should not ignore (blanks) under manager.

3. once this is sorted grandtotal from pivot needs to be pulled and published in to the column immediately after the last column in row 4 that contains data. in this case it is column "M". I have requested for the above code so that it might be easy for me to pull the hours using combination of senior managers and managers and also combination of senior managers and (blanks).

I also thought it might help me to figure additions or movements in terms of SM and managers by comparing combination of column A and Column B of both sheets.

 

[Fluff]

As this is now a totally different question, you will need to start a new thread.
Thanks

 

[Wafee]

Hi Fl

As this is now a totally different question, you will need to start a new thread.
Thanks

Hi Fluff, Thank you so much for your time. I have posted a new thread "VBA to map pivot data to another sheet". I would be greatfull if you can help.

 

[Fluff]

You're welcome & thanks for the feedback.