Simple loop in macro
 

Can someone please help me.

I'm trying to write a script which will go to adjacent cells in row 6
of a PivotTable and replace the existing text with text located in
adjacent cells in a row at another location.

I can achieve this with the following code:

Range("B5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_ ("4/12/2005 -
10/12/2005").Caption = "10/12/05"
Range("C5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_("1 1/12/2005 -
17/12/2005").Caption = "17/12/05"
Range("C5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_("1 8/12/2005 -
24/12/2005").Caption = "24/12/05"

However, I want to write this more efficiently using code which
successively increments each column reference in Row 6 and replacing
with the text in Row 100, where B100 = 10/12/05, C100 = 17/12/05, D100
= 24/12/05.

Can someone kindly help show me how this can be done?

Thanks, Simon

Simple loop in macro
 

Dim cell As Range
Dim n As Long

n = 4
For Each cell In Range("B5:D5")
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Date") _
.PivotItems_(n & "/12/2005 - " & n + 6 & "/12/2005") _
.Caption = n + 6 & "/12/05"
Next cell


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
Can someone please help me.

I'm trying to write a script which will go to adjacent cells in row 6
of a PivotTable and replace the existing text with text located in
adjacent cells in a row at another location.

I can achieve this with the following code:

Range("B5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_ ("4/12/2005 -
10/12/2005").Caption = "10/12/05"
Range("C5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_("1 1/12/2005 -
17/12/2005").Caption = "17/12/05"
Range("C5").Select
ActiveSheet.PivotTables
("PivotTable1").PivotFields("Date").PivotItems_("1 8/12/2005 -
24/12/2005").Caption = "24/12/05"

However, I want to write this more efficiently using code which
successively increments each column reference in Row 6 and replacing
with the text in Row 100, where B100 = 10/12/05, C100 = 17/12/05, D100
= 24/12/05.

Can someone kindly help show me how this can be done?

Thanks, Simon

Simple loop in macro
 

Bob

Many thanks for your reply. I've tried what you've said, and this works
for the first cell B5 but then the macro halts and I get an error
message.

What I want it to do after B5 is then move to C5 and replace text with
17/12/05, then move to D5 and replace text with 24/12/05.

Your help would be very much appreciated.

Thanks, Simon