Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Dear Ashish,
I was out of town so could not reply earlier
I found the mistake and now it is working fine.
super
thanks a lot
Rashid
On Sunday, October 14, 2012 1:24:38 PM UTC+4, ashish wrote:

> Hi 
>
> Its working fine on my desktop. Can you send the file 
>
> Also specify which version of office u r using
> Sent on my BlackBerry® from Vodafone
> --
> *From: * prkhan56 > 
> *Sender: * excel-...@googlegroups.com  
> *Date: *Sat, 13 Oct 2012 12:01:09 -0700 (PDT)
> *To: *>
> *ReplyTo: * excel-...@googlegroups.com  
> *Subject: *Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each 
> sheet in the workbook
>
> Dear Ashish,
> I am still waiting some guidance from your end
>  
> Regards
> Rashid khan
> On Tuesday, October 9, 2012 11:08:19 PM UTC+4, prkhan56 wrote:
>
>> Hello Ashish,
>> Any guidance to solve the problem I am facing?
>> Thanks 
>> Rashid Khan
>>
>> On Sunday, October 7, 2012 8:59:04 PM UTC+4, prkhan56 wrote:
>>
>>> Hello Ashish,
>>>
>>> When I run call_pivot
>>> It gives Run-time Error '9'
>>> Subscript out of range 
>>> and highlights the following line:
>>>
>>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>>
>>> Am I doing something wrong?
>>>
>>> Thanks for your time once again.
>>> Rashid 
>>>
>>> On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>>>>
>>>> try this
>>>>
>>>> Sub call_pivot()
>>>> Dim i As Long
>>>> For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
>>>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>>> Next
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Sub create_pivot_table(wk As Worksheet)
>>>>
>>>> Application.DisplayAlerts = False
>>>> Dim pt As PivotTable
>>>> Dim PTCache As PivotCache
>>>> Dim wks As Worksheet
>>>> For Each wks In ThisWorkbook.Sheets
>>>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>>>> wks.Delete
>>>> Exit For
>>>> End If
>>>> Next
>>>> 
>>>> wk.Select
>>>> Sheets.Add
>>>> ActiveSheet.Name = "PT" & wk.Name
>>>> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
>>>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
>>>> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>>>>
>>>> With pt
>>>> .PivotFields("No").Orientation = xlRowField
>>>> With .PivotFields("Score")
>>>> .Orientation = xlDataField
>>>> .Function = xlSum
>>>> .Position = 1
>>>> End With
>>>> With .PivotFields("Result")
>>>> .Orientation = xlDataField
>>>> .Function = xlSum
>>>> .Position = 2
>>>> End With
>>>> With .PivotFields("Skills %")
>>>> .Orientation = xlDataField
>>>> .Function = xlSum
>>>> .Position = 3
>>>> .NumberFormat = "0%"
>>>> End With
>>>> End With
>>>>
>>>> Application.DisplayAlerts = True
>>>> End Sub
>>>>
>>>>
>>>>
>>>>
>>>> On Sun, Oct 7, 2012 at 12:44 AM, prkhan56  wrote:
>>>>
>>>>> Hello Ashish,
>>>>> Thanks for the code. 
>>>>>  
>>>>> Do I have to do the looping as follows:
>>>>> Sub call_pivot()
>>>>> Call create_pivot_table(Sheets("**Participant1"))
>>>>> Call create_pivot_table(Sheets("**Participant2"))
>>>>> Call create_pivot_table(Sheets("**Participant3"))
>>>>> Call create_pivot_table(Sheets("**Participant4"))
>>>>> Call create_pivot_table(Sheets("**Participant5"))
>>>>> End Sub
>>>>>  
>>>>> Regards
>>>>> R

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[koul . ashish]

Hi 

Its working fine on my desktop. Can you send the file 

Also specify which version of office u r using
Sent on my BlackBerry® from Vodafone

-Original Message-
From: prkhan56 
Sender: excel-macros@googlegroups.com
Date: Sat, 13 Oct 2012 12:01:09 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet
 in the workbook

Dear Ashish,
I am still waiting some guidance from your end
 
Regards
Rashid khan
On Tuesday, October 9, 2012 11:08:19 PM UTC+4, prkhan56 wrote:

> Hello Ashish,
> Any guidance to solve the problem I am facing?
> Thanks 
> Rashid Khan
>
> On Sunday, October 7, 2012 8:59:04 PM UTC+4, prkhan56 wrote:
>
>> Hello Ashish,
>>
>> When I run call_pivot
>> It gives Run-time Error '9'
>> Subscript out of range 
>> and highlights the following line:
>>
>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>
>> Am I doing something wrong?
>>
>> Thanks for your time once again.
>> Rashid 
>>
>> On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>>>
>>> try this
>>>
>>> Sub call_pivot()
>>> Dim i As Long
>>> For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
>>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>> Next
>>> End Sub
>>>
>>>
>>>
>>> Sub create_pivot_table(wk As Worksheet)
>>>
>>> Application.DisplayAlerts = False
>>> Dim pt As PivotTable
>>> Dim PTCache As PivotCache
>>> Dim wks As Worksheet
>>> For Each wks In ThisWorkbook.Sheets
>>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>>> wks.Delete
>>> Exit For
>>> End If
>>> Next
>>> 
>>> wk.Select
>>> Sheets.Add
>>> ActiveSheet.Name = "PT" & wk.Name
>>> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
>>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
>>> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>>>
>>> With pt
>>> .PivotFields("No").Orientation = xlRowField
>>> With .PivotFields("Score")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 1
>>> End With
>>> With .PivotFields("Result")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 2
>>> End With
>>> With .PivotFields("Skills %")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 3
>>> .NumberFormat = "0%"
>>> End With
>>> End With
>>>
>>> Application.DisplayAlerts = True
>>> End Sub
>>>
>>>
>>>
>>>
>>> On Sun, Oct 7, 2012 at 12:44 AM, prkhan56  wrote:
>>>
>>>> Hello Ashish,
>>>> Thanks for the code. 
>>>>  
>>>> Do I have to do the looping as follows:
>>>> Sub call_pivot()
>>>> Call create_pivot_table(Sheets("**Participant1"))
>>>> Call create_pivot_table(Sheets("**Participant2"))
>>>> Call create_pivot_table(Sheets("**Participant3"))
>>>> Call create_pivot_table(Sheets("**Participant4"))
>>>> Call create_pivot_table(Sheets("**Participant5"))
>>>> End Sub
>>>>  
>>>> Regards
>>>> Rashid
>>>>
>>>> On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:
>>>>
>>>>> run a loop and pass sheet names to sub routine "create_pivot_table"
>>>>>
>>>>>
>>>>>
>>>>> Sub call_pivot()
>>>>> Call create_pivot_table(Sheets("**Participant1"))
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Sub create_pivot_table(wk As Worksheet)
>>>>>
>>>>> Application.DisplayAlerts = False
>>>>> Dim pt As PivotTable
>>>>> Dim PTCache As PivotCache
>>&g

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Dear Ashish,
I am still waiting some guidance from your end
 
Regards
Rashid khan
On Tuesday, October 9, 2012 11:08:19 PM UTC+4, prkhan56 wrote:

> Hello Ashish,
> Any guidance to solve the problem I am facing?
> Thanks 
> Rashid Khan
>
> On Sunday, October 7, 2012 8:59:04 PM UTC+4, prkhan56 wrote:
>
>> Hello Ashish,
>>
>> When I run call_pivot
>> It gives Run-time Error '9'
>> Subscript out of range 
>> and highlights the following line:
>>
>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>
>> Am I doing something wrong?
>>
>> Thanks for your time once again.
>> Rashid 
>>
>> On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>>>
>>> try this
>>>
>>> Sub call_pivot()
>>> Dim i As Long
>>> For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
>>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>> Next
>>> End Sub
>>>
>>>
>>>
>>> Sub create_pivot_table(wk As Worksheet)
>>>
>>> Application.DisplayAlerts = False
>>> Dim pt As PivotTable
>>> Dim PTCache As PivotCache
>>> Dim wks As Worksheet
>>> For Each wks In ThisWorkbook.Sheets
>>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>>> wks.Delete
>>> Exit For
>>> End If
>>> Next
>>> 
>>> wk.Select
>>> Sheets.Add
>>> ActiveSheet.Name = "PT" & wk.Name
>>> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
>>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
>>> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>>>
>>> With pt
>>> .PivotFields("No").Orientation = xlRowField
>>> With .PivotFields("Score")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 1
>>> End With
>>> With .PivotFields("Result")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 2
>>> End With
>>> With .PivotFields("Skills %")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 3
>>> .NumberFormat = "0%"
>>> End With
>>> End With
>>>
>>> Application.DisplayAlerts = True
>>> End Sub
>>>
>>>
>>>
>>>
>>> On Sun, Oct 7, 2012 at 12:44 AM, prkhan56  wrote:
>>>
 Hello Ashish,
 Thanks for the code. 
  
 Do I have to do the looping as follows:
 Sub call_pivot()
 Call create_pivot_table(Sheets("**Participant1"))
 Call create_pivot_table(Sheets("**Participant2"))
 Call create_pivot_table(Sheets("**Participant3"))
 Call create_pivot_table(Sheets("**Participant4"))
 Call create_pivot_table(Sheets("**Participant5"))
 End Sub
  
 Regards
 Rashid

 On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:

> run a loop and pass sheet names to sub routine "create_pivot_table"
>
>
>
> Sub call_pivot()
> Call create_pivot_table(Sheets("**Participant1"))
> End Sub
>
>
>
> Sub create_pivot_table(wk As Worksheet)
>
> Application.DisplayAlerts = False
> Dim pt As PivotTable
> Dim PTCache As PivotCache
> Dim wks As Worksheet
> For Each wks In ThisWorkbook.Sheets
> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
> wks.Delete
> Exit For
> End If
> Next
> 
> wk.Select
> Sheets.Add
> ActiveSheet.Name = "PT" & wk.Name
> Set PTCache = ActiveWorkbook.PivotCaches.**Create(xlDatabase, 
> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).**Row))
> Set pt = PTCache.CreatePivotTable(**Range("A1"), "Pivot1", True)
>
> With pt
> .PivotFields("No").Orientation = xlRowField
> With .PivotFields("Score")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 1
> End With
> With .PivotFields("Result")
>  .Orientation = xlDataField
> .Function = xlSum
> .Position = 2
> End With
> With .PivotFields("Skills %")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 3
> .NumberFormat = "0%"
> End With
> End With
>
> Application.DisplayAlerts = True
> End Sub
>
>
>
>
>
> On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  wrote:
>
>>
>>
>> On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>>>
>>> I posted this yesterday...it seems it did not get through..so 
>>> posting once again
>>>  
>>> Hello All,
>>>
>>> I am using Excel 2010. I have a List of Names in Sheet name “List”
>>>
>>>
>>> I have attached a file with two modules

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Hello Ashish,
Any guidance to solve the problem I am facing?
Thanks 
Rashid Khan

On Sunday, October 7, 2012 8:59:04 PM UTC+4, prkhan56 wrote:

> Hello Ashish,
>
> When I run call_pivot
> It gives Run-time Error '9'
> Subscript out of range 
> and highlights the following line:
>
> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>
> Am I doing something wrong?
>
> Thanks for your time once again.
> Rashid 
>
> On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>>
>> try this
>>
>> Sub call_pivot()
>> Dim i As Long
>> For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>> Next
>> End Sub
>>
>>
>>
>> Sub create_pivot_table(wk As Worksheet)
>>
>> Application.DisplayAlerts = False
>> Dim pt As PivotTable
>> Dim PTCache As PivotCache
>> Dim wks As Worksheet
>> For Each wks In ThisWorkbook.Sheets
>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>> wks.Delete
>> Exit For
>> End If
>> Next
>> 
>> wk.Select
>> Sheets.Add
>> ActiveSheet.Name = "PT" & wk.Name
>> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
>> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>>
>> With pt
>> .PivotFields("No").Orientation = xlRowField
>> With .PivotFields("Score")
>> .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 1
>> End With
>> With .PivotFields("Result")
>> .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 2
>> End With
>> With .PivotFields("Skills %")
>> .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 3
>> .NumberFormat = "0%"
>> End With
>> End With
>>
>> Application.DisplayAlerts = True
>> End Sub
>>
>>
>>
>>
>> On Sun, Oct 7, 2012 at 12:44 AM, prkhan56  wrote:
>>
>>> Hello Ashish,
>>> Thanks for the code. 
>>>  
>>> Do I have to do the looping as follows:
>>> Sub call_pivot()
>>> Call create_pivot_table(Sheets("**Participant1"))
>>> Call create_pivot_table(Sheets("**Participant2"))
>>> Call create_pivot_table(Sheets("**Participant3"))
>>> Call create_pivot_table(Sheets("**Participant4"))
>>> Call create_pivot_table(Sheets("**Participant5"))
>>> End Sub
>>>  
>>> Regards
>>> Rashid
>>>
>>> On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:
>>>
 run a loop and pass sheet names to sub routine "create_pivot_table"



 Sub call_pivot()
 Call create_pivot_table(Sheets("**Participant1"))
 End Sub



 Sub create_pivot_table(wk As Worksheet)

 Application.DisplayAlerts = False
 Dim pt As PivotTable
 Dim PTCache As PivotCache
 Dim wks As Worksheet
 For Each wks In ThisWorkbook.Sheets
 If UCase("PT" & wk.Name) = UCase(wks.Name) Then
 wks.Delete
 Exit For
 End If
 Next
 
 wk.Select
 Sheets.Add
 ActiveSheet.Name = "PT" & wk.Name
 Set PTCache = ActiveWorkbook.PivotCaches.**Create(xlDatabase, 
 wk.Range("a2:i" & wk.Range("a65356").End(xlUp).**Row))
 Set pt = PTCache.CreatePivotTable(**Range("A1"), "Pivot1", True)

 With pt
 .PivotFields("No").Orientation = xlRowField
 With .PivotFields("Score")
 .Orientation = xlDataField
 .Function = xlSum
 .Position = 1
 End With
 With .PivotFields("Result")
  .Orientation = xlDataField
 .Function = xlSum
 .Position = 2
 End With
 With .PivotFields("Skills %")
 .Orientation = xlDataField
 .Function = xlSum
 .Position = 3
 .NumberFormat = "0%"
 End With
 End With

 Application.DisplayAlerts = True
 End Sub





 On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  wrote:

>
>
> On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>>
>> I posted this yesterday...it seems it did not get through..so posting 
>> once again
>>  
>> Hello All,
>>
>> I am using Excel 2010. I have a List of Names in Sheet name “List”
>>
>>
>> I have attached a file with two modules.
>>
>>
>> *Module1* Macro creates separate sheet with the names listed in the 
>> sheet name List and copies Sheet Data for each name.
>>
>>
>> *Module2* makes Pivot Table on the Active Sheet.
>>
>>
>> My requirement is to amend the *Module2* as follows:
>>
>> 1) * **Modul

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Hello Ashish,

When I run call_pivot
It gives Run-time Error '9'
Subscript out of range 
and highlights the following line:

Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))

Am I doing something wrong?

Thanks for your time once again.
Rashid 

On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>
> try this
>
> Sub call_pivot()
> Dim i As Long
> For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
> Next
> End Sub
>
>
>
> Sub create_pivot_table(wk As Worksheet)
>
> Application.DisplayAlerts = False
> Dim pt As PivotTable
> Dim PTCache As PivotCache
> Dim wks As Worksheet
> For Each wks In ThisWorkbook.Sheets
> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
> wks.Delete
> Exit For
> End If
> Next
> 
> wk.Select
> Sheets.Add
> ActiveSheet.Name = "PT" & wk.Name
> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>
> With pt
> .PivotFields("No").Orientation = xlRowField
> With .PivotFields("Score")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 1
> End With
> With .PivotFields("Result")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 2
> End With
> With .PivotFields("Skills %")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 3
> .NumberFormat = "0%"
> End With
> End With
>
> Application.DisplayAlerts = True
> End Sub
>
>
>
>
> On Sun, Oct 7, 2012 at 12:44 AM, prkhan56 
> > wrote:
>
>> Hello Ashish,
>> Thanks for the code. 
>>  
>> Do I have to do the looping as follows:
>> Sub call_pivot()
>> Call create_pivot_table(Sheets("**Participant1"))
>> Call create_pivot_table(Sheets("**Participant2"))
>> Call create_pivot_table(Sheets("**Participant3"))
>> Call create_pivot_table(Sheets("**Participant4"))
>> Call create_pivot_table(Sheets("**Participant5"))
>> End Sub
>>  
>> Regards
>> Rashid
>>
>> On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:
>>
>>> run a loop and pass sheet names to sub routine "create_pivot_table"
>>>
>>>
>>>
>>> Sub call_pivot()
>>> Call create_pivot_table(Sheets("**Participant1"))
>>> End Sub
>>>
>>>
>>>
>>> Sub create_pivot_table(wk As Worksheet)
>>>
>>> Application.DisplayAlerts = False
>>> Dim pt As PivotTable
>>> Dim PTCache As PivotCache
>>> Dim wks As Worksheet
>>> For Each wks In ThisWorkbook.Sheets
>>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>>> wks.Delete
>>> Exit For
>>> End If
>>> Next
>>> 
>>> wk.Select
>>> Sheets.Add
>>> ActiveSheet.Name = "PT" & wk.Name
>>> Set PTCache = ActiveWorkbook.PivotCaches.**Create(xlDatabase, 
>>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).**Row))
>>> Set pt = PTCache.CreatePivotTable(**Range("A1"), "Pivot1", True)
>>>
>>> With pt
>>> .PivotFields("No").Orientation = xlRowField
>>> With .PivotFields("Score")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 1
>>> End With
>>> With .PivotFields("Result")
>>>  .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 2
>>> End With
>>> With .PivotFields("Skills %")
>>> .Orientation = xlDataField
>>> .Function = xlSum
>>> .Position = 3
>>> .NumberFormat = "0%"
>>> End With
>>> End With
>>>
>>> Application.DisplayAlerts = True
>>> End Sub
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  wrote:
>>>


 On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>
> I posted this yesterday...it seems it did not get through..so posting 
> once again
>  
> Hello All,
>
> I am using Excel 2010. I have a List of Names in Sheet name “List”
>
>
> I have attached a file with two modules.
>
>
> *Module1* Macro creates separate sheet with the names listed in the 
> sheet name List and copies Sheet Data for each name.
>
>
> *Module2* makes Pivot Table on the Active Sheet.
>
>
> My requirement is to amend the *Module2* as follows:
>
> 1) * **Module2* should loop through each name and create Pivot Table 
> for each sheet created and name it viz. Pivot created for Name1 should be 
> PivotName1, Pivot created for Name2 should be PivotName2 and so on.
>
> 2) The macro should exclude Data and List sheets.
>
> Any help would be appreciated.
>
>>

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[ashish koul]

try this

Sub call_pivot()
Dim i As Long
For i = 2 To Sheets("List").Range("a65356").End(xlUp).Row
Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
Next
End Sub



Sub create_pivot_table(wk As Worksheet)

Application.DisplayAlerts = False
Dim pt As PivotTable
Dim PTCache As PivotCache
Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
If UCase("PT" & wk.Name) = UCase(wks.Name) Then
wks.Delete
Exit For
End If
Next

wk.Select
Sheets.Add
ActiveSheet.Name = "PT" & wk.Name
Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase,
wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)

With pt
.PivotFields("No").Orientation = xlRowField
With .PivotFields("Score")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With .PivotFields("Result")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
End With
With .PivotFields("Skills %")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "0%"
End With
End With

Application.DisplayAlerts = True
End Sub




On Sun, Oct 7, 2012 at 12:44 AM, prkhan56  wrote:

> Hello Ashish,
> Thanks for the code.
>
> Do I have to do the looping as follows:
> Sub call_pivot()
> Call create_pivot_table(Sheets("**Participant1"))
> Call create_pivot_table(Sheets("**Participant2"))
> Call create_pivot_table(Sheets("**Participant3"))
> Call create_pivot_table(Sheets("**Participant4"))
> Call create_pivot_table(Sheets("**Participant5"))
> End Sub
>
> Regards
> Rashid
>
> On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:
>
>> run a loop and pass sheet names to sub routine "create_pivot_table"
>>
>>
>>
>> Sub call_pivot()
>> Call create_pivot_table(Sheets("**Participant1"))
>> End Sub
>>
>>
>>
>> Sub create_pivot_table(wk As Worksheet)
>>
>> Application.DisplayAlerts = False
>> Dim pt As PivotTable
>> Dim PTCache As PivotCache
>> Dim wks As Worksheet
>> For Each wks In ThisWorkbook.Sheets
>> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
>> wks.Delete
>> Exit For
>> End If
>> Next
>>
>> wk.Select
>> Sheets.Add
>> ActiveSheet.Name = "PT" & wk.Name
>> Set PTCache = ActiveWorkbook.PivotCaches.**Create(xlDatabase,
>> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).**Row))
>> Set pt = PTCache.CreatePivotTable(**Range("A1"), "Pivot1", True)
>>
>> With pt
>> .PivotFields("No").Orientation = xlRowField
>> With .PivotFields("Score")
>> .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 1
>> End With
>> With .PivotFields("Result")
>>  .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 2
>> End With
>> With .PivotFields("Skills %")
>> .Orientation = xlDataField
>> .Function = xlSum
>> .Position = 3
>> .NumberFormat = "0%"
>> End With
>> End With
>>
>> Application.DisplayAlerts = True
>> End Sub
>>
>>
>>
>>
>>
>> On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  wrote:
>>
>>>
>>>
>>> On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:

 I posted this yesterday...it seems it did not get through..so posting
 once again

 Hello All,

 I am using Excel 2010. I have a List of Names in Sheet name “List”


 I have attached a file with two modules.


 *Module1* Macro creates separate sheet with the names listed in the
 sheet name List and copies Sheet Data for each name.


 *Module2* makes Pivot Table on the Active Sheet.


 My requirement is to amend the *Module2* as follows:

 1) * **Module2* should loop through each name and create Pivot Table
 for each sheet created and name it viz. Pivot created for Name1 should be
 PivotName1, Pivot created for Name2 should be PivotName2 and so on.

 2) The macro should exclude Data and List sheets.

 Any help would be appreciated.

 Thanks

 Rashid Khan

>>>  --
>>> Join official facebook page of this forum @ https://www.facebook.com/**
>>> discussexcel 
>>>
>>> FORUM RULES (1120+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding bre

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Hello Ashish,
Thanks for the code. 
 
Do I have to do the looping as follows:
Sub call_pivot()
Call create_pivot_table(Sheets("Participant1"))
Call create_pivot_table(Sheets("Participant2"))
Call create_pivot_table(Sheets("Participant3"))
Call create_pivot_table(Sheets("Participant4"))
Call create_pivot_table(Sheets("Participant5"))
End Sub
 
Regards
Rashid

On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote:

> run a loop and pass sheet names to sub routine "create_pivot_table"
>
>
>
> Sub call_pivot()
> Call create_pivot_table(Sheets("Participant1"))
> End Sub
>
>
>
> Sub create_pivot_table(wk As Worksheet)
>
> Application.DisplayAlerts = False
> Dim pt As PivotTable
> Dim PTCache As PivotCache
> Dim wks As Worksheet
> For Each wks In ThisWorkbook.Sheets
> If UCase("PT" & wk.Name) = UCase(wks.Name) Then
> wks.Delete
> Exit For
> End If
> Next
> 
> wk.Select
> Sheets.Add
> ActiveSheet.Name = "PT" & wk.Name
> Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, 
> wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
> Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)
>
> With pt
> .PivotFields("No").Orientation = xlRowField
> With .PivotFields("Score")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 1
> End With
> With .PivotFields("Result")
>  .Orientation = xlDataField
> .Function = xlSum
> .Position = 2
> End With
> With .PivotFields("Skills %")
> .Orientation = xlDataField
> .Function = xlSum
> .Position = 3
> .NumberFormat = "0%"
> End With
> End With
>
> Application.DisplayAlerts = True
> End Sub
>
>
>
>
>
> On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  >wrote:
>
>>
>>
>> On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>>>
>>> I posted this yesterday...it seems it did not get through..so posting 
>>> once again
>>>  
>>> Hello All,
>>>
>>> I am using Excel 2010. I have a List of Names in Sheet name “List”
>>>
>>>
>>> I have attached a file with two modules.
>>>
>>>
>>> *Module1* Macro creates separate sheet with the names listed in the 
>>> sheet name List and copies Sheet Data for each name.
>>>
>>>
>>> *Module2* makes Pivot Table on the Active Sheet.
>>>
>>>
>>> My requirement is to amend the *Module2* as follows:
>>>
>>> 1) * **Module2* should loop through each name and create Pivot Table 
>>> for each sheet created and name it viz. Pivot created for Name1 should be 
>>> PivotName1, Pivot created for Name2 should be PivotName2 and so on.
>>>
>>> 2) The macro should exclude Data and List sheets.
>>>
>>> Any help would be appreciated.
>>>
>>> Thanks 
>>>
>>> Rashid Khan
>>>
>>  -- 
>> Join official facebook page of this forum @ 
>> https://www.facebook.com/discussexcel
>>  
>> FORUM RULES (1120+ members already BANNED for violation)
>>  
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
>> will not get quick attention or may not be answered.
>>  
>> 2) Don't post a question in the thread of another member.
>>  
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>  
>> 4) Acknowledge the responses you receive, good or bad.
>>  
>> 5) Cross-promotion of, or links to, forums competitive to this forum in 
>> signatures are prohibited. 
>>  
>> 6) Jobs posting is not allowed.
>>  
>> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>>  
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum 
>> owners and members are not responsible for any loss.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "MS EXCEL AND VBA MACROS" group.
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> To unsubscribe from this group, send email to 
>> excel-macros...@googlegroups.com .
>>  
>>  
>>
>
>
>
> -- 
> *Regards*
> * *
> *Ashish Koul*
>
>
> *Visit*
> *http://www.excelvbamacros.com/*
> *http://www.accessvbamacros.com/*
>
> P Before printing, think about the environment.
>
>  
>
>

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

6) Jobs po

Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[ashish koul]

run a loop and pass sheet names to sub routine "create_pivot_table"



Sub call_pivot()
Call create_pivot_table(Sheets("Participant1"))
End Sub



Sub create_pivot_table(wk As Worksheet)

Application.DisplayAlerts = False
Dim pt As PivotTable
Dim PTCache As PivotCache
Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
If UCase("PT" & wk.Name) = UCase(wks.Name) Then
wks.Delete
Exit For
End If
Next

wk.Select
Sheets.Add
ActiveSheet.Name = "PT" & wk.Name
Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase,
wk.Range("a2:i" & wk.Range("a65356").End(xlUp).Row))
Set pt = PTCache.CreatePivotTable(Range("A1"), "Pivot1", True)

With pt
.PivotFields("No").Orientation = xlRowField
With .PivotFields("Score")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With .PivotFields("Result")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
End With
With .PivotFields("Skills %")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "0%"
End With
End With

Application.DisplayAlerts = True
End Sub





On Thu, Oct 4, 2012 at 1:20 AM, prkhan56  wrote:

>
>
> On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>>
>> I posted this yesterday...it seems it did not get through..so posting
>> once again
>>
>> Hello All,
>>
>> I am using Excel 2010. I have a List of Names in Sheet name “List”
>>
>>
>> I have attached a file with two modules.
>>
>>
>> *Module1* Macro creates separate sheet with the names listed in the
>> sheet name List and copies Sheet Data for each name.
>>
>>
>> *Module2* makes Pivot Table on the Active Sheet.
>>
>>
>> My requirement is to amend the *Module2* as follows:
>>
>> 1) * **Module2* should loop through each name and create Pivot Table for
>> each sheet created and name it viz. Pivot created for Name1 should be
>> PivotName1, Pivot created for Name2 should be PivotName2 and so on.
>>
>> 2) The macro should exclude Data and List sheets.
>>
>> Any help would be appreciated.
>>
>> Thanks
>>
>> Rashid Khan
>>
>  --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>



-- 
*Regards*
* *
*Ashish Koul*


*Visit*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/*

P Before printing, think about the environment.

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.

$$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:
>
> I posted this yesterday...it seems it did not get through..so posting once 
> again
>  
> Hello All,
>
> I am using Excel 2010. I have a List of Names in Sheet name “List”
>
>
> I have attached a file with two modules.
>
>
> *Module1* Macro creates separate sheet with the names listed in the sheet 
> name List and copies Sheet Data for each name.
>
>
> *Module2* makes Pivot Table on the Active Sheet.
>
>
> My requirement is to amend the *Module2* as follows:
>
> 1) * **Module2* should loop through each name and create Pivot Table for 
> each sheet created and name it viz. Pivot created for Name1 should be 
> PivotName1, Pivot created for Name2 should be PivotName2 and so on.
>
> 2) The macro should exclude Data and List sheets.
>
> Any help would be appreciated.
>
> Thanks 
>
> Rashid Khan
>

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.

$$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook

[prkhan56]

Hello Ashsh,
I posted my query and dont see it
Did it get through.
Rashid
On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote:

> I posted this yesterday...it seems it did not get through..so posting once 
> again
>  
> Hello All,
>
> I am using Excel 2010. I have a List of Names in Sheet name “List”
>
>
> I have attached a file with two modules.
>
>
> *Module1* Macro creates separate sheet with the names listed in the sheet 
> name List and copies Sheet Data for each name.
>
>
> *Module2* makes Pivot Table on the Active Sheet.
>
>
> My requirement is to amend the *Module2* as follows:
>
> 1) * **Module2* should loop through each name and create Pivot Table for 
> each sheet created and name it viz. Pivot created for Name1 should be 
> PivotName1, Pivot created for Name2 should be PivotName2 and so on.
>
> 2) The macro should exclude Data and List sheets.
>
> Any help would be appreciated.
>
> Thanks 
>
> Rashid Khan
>

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.