Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: Loop through list in Excel to run Macro

    Hello, I want to be able to loop through my macro so that it runs for each state I have listed. Can someone please help? I'm not sure where and how to put the code. Here is the code that I currently have. I want to be able to do the first state on my list first, run the whole procedure, then loop back to the top and do it again for the next state and so on. Many thanks.
    Sub RunWeeklyReport()
    '
    ' RunWeeklyReport Macro
    ' Macro recorded
    '

    '
    Dim FPath As String, FName As String, FName2 As String
    Dim CellValue As String
    Dim NewFilter As String

    FPath = "C:\Documents and Settings\Update\Weekly Reports by State\"
    FName = "Activity_"
    FName2 = "Weekly Update.xls"
    CellValue = Workbooks("Weekly Macro.xls").Worksheets("Sheet1").Range("C1").Text
    NewFilter = Workbooks("Weekly Macro.xls").Worksheets("Sheet1").Range("B3").Text

    Workbooks.Open FPath & FName & NewFilter & ".xls"
    Windows("Weekly Macro").Activate
    Workbooks.Open FPath & FName2
    Sheets("Weekly Lead Update").Copy Before:=Workbooks(FName & NewFilter & ".xls").Sheets(1)
    Range("A6:M6").Select
    With Selection.Interior
    .ColorIndex = 34
    .Pattern = xlSolid
    End With
    Windows(FName2).Activate
    ActiveWindow.Close
    Windows(FName & NewFilter & ".xls").Activate
    Range("A1").Select
    ActiveSheet.Next.Select
    ActiveWorkbook.Names.Add Name:="StatusData", RefersToR1C1:= _
    "=OFFSET(qryByStatus!R1C1,0,0,COUNTA(qryByStatus!C 1),5)"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "StatusData").CreatePivotTable TableDestination:="", _
    TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("Territory" _
    , "Name of Current User"), ColumnFields:="Status", PageFields:="State"
    ActiveSheet.PivotTables("PivotTable3").PivotFields ("CountOfInteraction ID"). _
    Orientation = xlDataField
    ActiveSheet.PivotTables("PivotTable3").PivotFields ("State").CurrentPage = NewFilter
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Data by Status"
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Data by Status - by Territory, User, Status"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Data thru"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "='[Weekly Macro.xls]Sheet1'!R1C2"
    Range("B2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    End With
    Range("A1:B2").Select
    Selection.Font.Bold = True
    With Selection.Font
    .Name = "MS Sans Serif"
    .Size = 12
    End With
    Range("A4:B4").Select
    Selection.Font.Bold = True
    With Selection.Interior
    .ColorIndex = 34
    .Pattern = xlSolid
    End With
    Range("A6:G6").Select
    Selection.Delete Shift:=xlUp
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Font.Bold = True
    With Selection.Interior
    .ColorIndex = 34
    .Pattern = xlSolid
    End With
    Columns("G:G").EntireColumn.AutoFit
    Columns("A:A").ColumnWidth = 16
    Range("A1").Select
    ActiveSheet.Next.Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:= _
    FPath & FName & NewFilter & " " & CellValue & ".xls" _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

  2. #2
    Join Date
    Aug 2010
    Posts
    3

    Never mind, I got it.

    Never mind on my earlier post; I was able to solve it. But now the problem I run into is I have the macro to run pivot tables. But for some of the states on some of the reports, there is no data. How do I bypass this so the macro doesn't get stopped on that pivot table and allow it to continue on?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •