×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Running Code for every value in a column

Running Code for every value in a column

Running Code for every value in a column

(OP)
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

CODE -->

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 

RE: Running Code for every value in a column

>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" ponder

---- Andy

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

RE: Running Code for every value in a column

CODE

'...
For i = 1 To rng.Rows.Count
[SelectedManager].Value = rng.Cells(i, 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...

CODE

Set rng = [tMANAGERS[Manager]]
'...
For i[/b] = 1 To rng.Rows.Count
[SelectedManager].Value = rng.Cells([b]i, 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Running Code for every value in a column

(OP)
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

CODE

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 

RE: Running Code for every value in a column

> 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:

CODE

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

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

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close