For each loop in VBA

[JohanR]

Hey!

I'm quite new to VBA and would really appreciate some help to solve a problem I ran into. I have a dataset that looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]SCHOOL[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Johnny School[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Inga[/TD]
[TD]Jimmy School[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Johnny[/TD]
[TD]Carrie School[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]Beth[/TD]
[TD]Carrie School[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Johnny School[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Jimmy School[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]


This is just a small sample of my data. There are like 500 different schools in there and I would like to loop through all unique values in the school column. I'll then use it to create a pdf with scores for each school. I'll look into that procedure at a later stage, but for now I need to know how to set up a proper loop.

I've looked at various examples and I understand that I need to declare an array and then use a "for each" loop.

But I don't really understand how to declare an array from the unique values in a column. I don't want to write all the school names down. Can someone help me out?

Thanks in advance!

Johan

 

[JohanR]

I have an additional question.

I use the code below to filter out the results (from a pivot table) for every school in my data and then copy the results into a unique sheet for every school. It works great the first time I run it.

But if I run the macro again, the school sheets end up blank. It only works if I manually delete all school sheets before I run the macro. Can someone explain how to make it work without having to delete all the sheets first?

Sub Macroname()


  Dim dict As Object
  Dim cel As Range
  Dim ky As Variant
  
  Dim pf As PivotField
  
  '-- Declaring name of pivot table
  Dim ptable As String
  
    ptable = "Pivottabell7"
  
    '--Activate the result sheet
 Sheets("Blad1").Select
    
  Set pf = ActiveSheet.PivotTables(ptable).PivotFields("School")


  '--Establish dictionary object
  Set dict = CreateObject("Scripting.Dictionary")
  
  '--Ensure it is not Case-sensitive
  dict.CompareMode = 1
  
  '--Put the school names into dictionary, they will not be repeated.
  For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
    dict(cel.Value) = 1 '(The 1 isn't important, we just needed something)
  Next cel
  
  'Just to demo cycling through the results
  For Each ky In dict.Keys
   'Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ky
   
  '--Clear Out Any Previous Filtering
  pf.ClearAllFilters


  '--choose year
  pf.CurrentPage = ky
   
  '--Select pivot table and copy.
  ActiveSheet.PivotTables(ptable).TableRange1.Select
  ActiveSheet.PivotTables(ptable).TableRange1.Copy
  
  '--Create school sheet. If it already exists, then delete and create new.
    Dim mySheetName As String
    mySheetName = ky
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets(mySheetName).Delete
    Err.Clear
    Application.DisplayAlerts = True
    Worksheets.Add.Name = mySheetName
    'MsgBox "The sheet named ''" & mySheetName & "'' has been replaced."
  
  
   '--Activate result sheet.
  Sheets(mySheetName).Select
 
  '--Paste in the target destination


  Sheets(mySheetName).Paste
  Application.CutCopyMode = False
  
  '--Locate last row
  Dim LastRow As Long
  LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2).Row


  '--Add the name of the school
  ActiveSheet.Cells(LastRow, 1).Value = ky


 


  '--AutoFit All Columns on Worksheet
    ThisWorkbook.Worksheets(ky).Cells.EntireColumn.AutoFit
    
  
  '--PDF export
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
  Filename:="C:\test\" & ky & ".pdf", _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=False
  
  
  '--activate result sheet before loop starts over.
  Sheets("Blad1").Select
   
  Next ky
      
End Sub