# VBA autofilter dynamic values, print & loop



## Super Mario (Dec 15, 2022)

Hi,

The aim is to create a macro to filter by sales orders (column H) and print the rows shown. However, the sales orders will change each day, as will the table size. Example, there are 4 different sales orders, so I would look to filter each sales order, print the contents and move to the next sales order and repeat until the end. 

Help on this will be greatly appreciated.


----------



## HaHoBe (Dec 15, 2022)

Hi Super Mario,

how about


```
Public Sub MrE_1224715_1615D12()
' https://www.mrexcel.com/board/threads/vba-autofilter-dynamic-values-print-loop.1224715/
  Dim rngCell As Range
  Dim lngCounter As Long
  Dim objDic As Object
  Dim wksPrint As Worksheet
  
  'adding a new sheet for collecting data for printozt, change name to suit
  Worksheets.Add.Name = "Temp"
  Set wksPrint = ActiveSheet
  Set objDic = CreateObject("scripting.dictionary")
  'change the name of the sheet to suit
  With Worksheets("Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    For Each rngCell In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
      objDic.Item(rngCell.Value) = vbEmpty
    Next rngCell
    
    For lngCounter = 0 To objDic.Count - 1
      .Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:=objDic.Keys()(lngCounter)
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      wksPrint.Range("A1").PasteSpecial xlPasteValues
      wksPrint.PrintOut
      wksPrint.UsedRange.ClearContents
    Next lngCounter
    .AutoFilterMode = False
  End With
  Application.DisplayAlerts = False
  wksPrint.Delete
  Application.DisplayAlerts = True
  Set wksPrint = Nothing
End Sub
```

Ciao,
Holger


----------



## Super Mario (Dec 16, 2022)

Hi Holger,

Thank you for the reply. It does work as expected, thanks. 

Can you help me understand what a public sub is and if this macro can be performed on a private sub?

BR
Super Mario


----------



## Super Mario (Dec 16, 2022)

Hi Holger,

2nd question - As the macro takes the data into a new tab to print, it loses the formatting of the table and column widths which prints out like the attached. Is there a way to retain the formatting, or set new formatting to space the cells out and include borders?

BR
Super Mario


----------



## HaHoBe (Dec 16, 2022)

Hi Super Mario,

a Private Sub can only be used in the current module while a Public Sub can be used in all (regarding modules). A Private Sub will not be listed in the View Macro Box.

Just add 1 codeline to the procedure:


```
Public Sub MrE_1224715_1615D12_Vers2()
' https://www.mrexcel.com/board/threads/vba-autofilter-dynamic-values-print-loop.1224715/
  Dim rngCell As Range
  Dim lngCounter As Long
  Dim objDic As Object
  Dim wksPrint As Worksheet
  
  'adding a new sheet for collecting data for printozt, change name to suit
  Worksheets.Add.Name = "Temp"
  Set wksPrint = ActiveSheet
  Set objDic = CreateObject("scripting.dictionary")
  'change the name of the sheet to suit
  With Worksheets("Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    For Each rngCell In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
      objDic.Item(rngCell.Value) = vbEmpty
    Next rngCell
    For lngCounter = 0 To objDic.Count - 1
      .Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:=objDic.Keys()(lngCounter)
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      '/// copy Values
      wksPrint.Range("A1").PasteSpecial xlPasteValues
      '/// copy Formats
      wksPrint.Range("A1").PasteSpecial xlPasteFormats
      wksPrint.PrintOut
      wksPrint.UsedRange.ClearContents
    Next lngCounter
    .AutoFilterMode = False
  End With
  Application.DisplayAlerts = False
  wksPrint.Delete
  Application.DisplayAlerts = True
  Set wksPrint = Nothing
End Sub
```

Ciao,
Holger


----------



## HaHoBe (Dec 16, 2022)

Hi,

and you should switch from clearing the Values when using


```
wksPrint.UsedRange.ClearContents
```

to clearing Formats as well when using


```
wksPrint.UsedRange.Clear
```

Holger


----------



## Super Mario (Dec 16, 2022)

Hi Holger,

Thanks - If I wanted to select an existing tab instead of creating a new "Temp" tab, what change would be required to the code?

BR
Super Mario


----------



## HaHoBe (Dec 16, 2022)

Hi Super Mario,

instead of


```
'adding a new sheet for collecting data for printozt, change name to suit
  Worksheets.Add.Name = "Temp"
  Set wksPrint = ActiveSheet
```

use something like (correct the name to suit)

```
Set wksPrint = Worksheets("Temp")
```

and make sure to either delete or comment the codelines


```
Application.DisplayAlerts = False
  wksPrint.Delete
  Application.DisplayAlerts = True
```

if you want to keep the sheet.

Holger


----------



## Super Mario (Dec 16, 2022)

Hi Holger,

Great, this is working well. I think that's all the changes needed! Thanks very much for your help.

BR
Super Mario


----------

