Running Code for every value in a column 1

[EliseFreedman]

Hi There

I have got a list of managers in my spreadsheet. I am trying to cycle through the range and load each manager into a specific cell which I am then using as the basis to run a series of macros (filtering the data in two sheets, copying the data into another workbook, creating pivot tables based on the copied data then finally saving the workbook) before moving onto the next name in the list and repeating the process. The code works for the first name in the list but then never moves to the next name in the list. Not sure what I am doing wrong. Can anyone help as it has been driving me nuts all afternoon.

I am using the code below

Dim rng As Range
Set rng = Application.Range("Search!T1:T38")
Dim i As Integer
For i = 1 To rng.Rows.Count
Range("C5").Value = rng.Cells.Value ' C5 has the name of the manager i am filtering by
Call FilterRangeCriteria
Call CopyFilteredData
Call CopyFilteredData2
Next i

 

[Andrzejek]

>The code works for the first name in the list but then never moves to the next name in the list
>C5 has the name of the manager i am filtering by

So, where in your code do you move/point "to the next name in the list"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

'...
For [b]i[/b] = 1 To rng.Rows.Count
[SelectedManager].Value = rng.Cells([b]i[/b], 1).Value
'...
Next

BTW, your code would be better for understandability and maintainable, if you were to use Named Ranges and Structured Tables.
For instance, if your table of managers were named tMANAGERS with a heading Manager and range C5 was named SelectedManager, then...

Set rng = [tMANAGERS[Manager]]
'...
For [b]i[/[s][/s]b] = 1 To rng.Rows.Count
[SelectedManager].Value = rng.Cells([b]i[/b], 1).Value
'...
Next

In fact I'd go further to make this simpler. I often had at least one VeryHidden sheet on which I put lots of reference stuff. I'd designate a NamedRange cell called ManagerListIndex and that's where I'd assign your For...Next index. Then, on that sheet, I'd use the SheetChange Event to run...
Call FilterRangeCriteria
Call CopyFilteredData
Call CopyFilteredData2

In this way, you'd have a way to run any Manager, by just assigning their list index to ManagerListIndex by hand or any other way.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

Brilliant. Its working now

The final bit of the puzzle is that i am creating a pivot table on the fly for each of the two worksheets in each file. There are some managers where there is only data for one of the worksheets which means that the pivot table for that worksheet is blank. I get an error when it tries to create the blank pivot table. to overcome this i created each pivot table on a separate sheet which worked ok. However, ideally, i would like to take the two pivot tables and put them on the same sheet if they exist and the delete the two single sheets. However, I am getting a 1004 error when it doesnt find one of the pivot tables (which is blank). How can I bypass that section of the code of the pivot table is blank

On Error GoTo ErrorHandler
'
    Windows("FilteredResults.xlsx").Activate
    Sheets("Incident Status Report").Select
    Sheets("Incident Status Report").Move Before:=Sheets(2)
    Sheets("Inspection Status Report").Select
    Sheets.Add.Name = "ActionSummary"
     Sheets("PivotInspections").Select
    ActiveSheet.PivotTables("PivotTable4").PivotSelect _
        "Status[All] 'Due Date'[All] 'Inspection No'[All] 'Gary Fox':'Stephen Bell' 'Column Grand Total'" _
        , xlDataAndLabel + xlFirstRow, True
    Range("C7").Select
    ActiveSheet.PivotTableWizard TableDestination:= _
        "[FilteredResults.xlsx]ActionSummary!R2C2"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Inspection Summary"
    Range("B1:C1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Sheets("PivotIncidents").Select
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Incident Number'[All]", _
        xlLabelOnly + xlFirstRow, True
    ActiveSheet.PivotTableWizard TableDestination:= _
        "[FilteredResults.xlsx]ActionSummary!R2C6"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Incident Summary"
    Range("F1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Sheets("PivotInspections").Delete
Sheets("PivotIncidents").Delete
ErrorHandler:
    Exit Sub

 

[Andrzejek]

> I am getting a 1004 error when it doesnt find one of the pivot tables (which is blank). How can I bypass that section of the code of the pivot table is blank

That what error handlers are for:

[blue]Public Sub SomeName()[/blue]
On Error GoTo ErrorHandler
Windows("FilteredResults.xlsx").Activate
...
Sheets("PivotIncidents").Delete
[blue]Exit Sub [/blue]

ErrorHandler:[blue]
If Err.Number = 1004 Then
    'Do some error magic here...
End If
End Sub[/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson