Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Macro to run for each change in column B

    Hi All,

    I have created a macro that look at a sheet called global extract and puts a filter in column 2 and then prompts the user to enter a number which matches to column 2 and proceeds with the macro.

    Once a week there is a requirement for the user to run the macro for all 30-50 numbers in the sheet so they have to enter this each time. Is there a way that it can look at the sheet called global extract and pass the value in from B2 into the input box and then go to the next value and loop until the end?

    Dim Ans1

    Ans1 = InputBox("What account number do you wish to generate the statement for?")
    'MsgBox "You will now see all open records for account " & Ans1 & "."


    Sheets("Global Extract").Select
    Range("A1").AutoFilter Field:=2, Criteria1:="=" & Ans1, _
    Operator:=xlAnd


    i.e. global extact

    ACCOUNT_NUMBER_GROUP
    8
    11
    39
    46
    50
    59
    60
    63
    69
    110
    126
    128
    131
    234
    272
    506
    511
    516
    616



    Thanks

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Stimpson,

    The following code will cycle through the rows that have not been filtered out and pass their values to Macro1. There is no need for an input box. It will start from row 2 in column B to the last value in column 2. Change the 2 as the starting row and rplace Macro1 to suit your needs.

    HTH,
    Maud


    Code:
    Public Sub AccountNum()
    LastRow = Worksheets("Global Extract").Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
        If Cells(I, 2).EntireRow.Hidden = False Then
            Ans1 = Cells(I, 2)
            Macro1 Ans1
        End If
    Next I
    End Sub
    
    
    
    Public Sub Macro1(num)
    MsgBox num
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a generic macro which will loop through all the entries in a filtered list. It does not require a pre-existing list of entries. You didn't state what the macro you ran did, so I just created one that print previews the filtered list, you can change the name as needed to call your macro.

    Code:
    Option Explicit
    Sub MacroWithAutofilter()
      Dim wks As Worksheet
      Dim PT As PivotTable
      Dim rPT As Range
      Dim rCell As Range
      Dim iCol As Integer
      Dim sHeader As String
      On Error GoTo Errhandler
      Application.ScreenUpdating = False
      iCol = 2 'Filter each item on Col B
      
      Set wks = ActiveSheet
      With wks
      If Not .AutoFilterMode Then
        .Range("a1").AutoFilter
      End If
      If .FilterMode Then .ShowAllData
      
      Set PT = .PivotTableWizard _
        (SourceType:=xlDatabase, _
        SourceData:=.Range("a1").CurrentRegion, _
          TableDestination:="", _
          TableName:="PivotTable1")
        sHeader = .Cells(1, iCol)
      With PT
        .AddFields RowFields:=sHeader
        .PivotFields(sHeader).Orientation = xlDataField
        .ColumnGrand = False
      End With
      With ActiveSheet
        Set rPT = .Range(.Range("A3"), _
              .Cells(.Rows.Count, 1).End(xlUp))
      End With
        For Each rCell In rPT
          .Range("a1").AutoFilter Field:=iCol, _
            Criteria1:=rCell.Value
          MyMacro (wks.Name)
        Next
      .ShowAllData
      End With
      Application.DisplayAlerts = False
      ActiveSheet.Delete
    
    ExitHandler:
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      Set rCell = Nothing
      Set rPT = Nothing
      Set PT = Nothing
      Set wks = Nothing
      Exit Sub
    
    Errhandler:
      MsgBox Err.Number & ":" & Err.Description
      Resume ExitHandler
    End Sub
    Sub MyMacro(sWks As String)
      Worksheets(sWks).PrintOut preview:=True
    End Sub
    Steve

  4. #4
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the responces. Basically i have a macro that produces a statement of account for a client depending on what 5the user types in the message box (Ans1). Ans1 is then matched to the detail sheet to put a filter on it and runs the macro.

    Wht i need is for each week we wish to print out all the statement without the user needing to input the number, i.e. on a seperate sheet called Accounts i want the macro to run the statement for tem continusly.

    Each Ans1 records is saved as an individual sheet and will not be duplicated in the Accounts tab.

    Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In my code if you replace the line
    MyMacro (wks.Name)

    with
    .PrintOut

    It will printout each individual item from column B...

    Steve

  6. #6
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks Steve,

    Slighly confused so apolgies, your macro goes on to run a pivot etc, i only want to pass a value into the ANS1 value and then run my macor and then at the end of my maco loop through the accounts sheet and goto the next line, i.e. line 2.

    Thanks

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The pivot table is created temporarily and then deleted. It is the means that the unique list of filtered entries is obtained at run time.

    If you don't need an updated list at runtime, you can use Maudibe's code which uses the list that is already created. For me the technique of not having to create and keep that list updated is more generic.

    Steve

Posting Permissions

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