Looping through unique values in a list to print worksheets matching the criteria

[riazd]

Hi

I'm trying to, loop through a list on my index sheet which contains the worksheet names and a location, and print to PDF for each unique location. Currently the macro is manually making each unique value equal the location, but i would like to automate it,


The code that i'm using is as follows viz.

Option Explicit


Sub GroupSheets()
Dim ShtGroup() As String
Dim Lr As Long
Dim ShtName As String
Dim n As Long
Dim p As Range
Dim c As Range


Lr = Range("A:B").find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row




For Each p In Sheets("Index").Range("A2:A" & Lr)
If (p.Offset(, 1)) = "VER" Then
ShtName = p.Value
n = n + 1
ReDim Preserve ShtGroup(1 To n)
ShtGroup = ShtName
End If
Next
Sheets(ShtGroup).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\riazd.PLAZA\Desktop\Reporting\Newfolder" & "VER" & "Payslips", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


For Each p In Sheets("Index").Range("A2:A" & Lr)
If (p.Offset(, 1)) = "FAC" Then
ShtName = p.Value
n = n + 1
ReDim Preserve ShtGroup(1 To n)
ShtGroup = ShtName
End If
Next
Sheets(ShtGroup).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\riazd.PLAZA\Desktop\Reporting\Newfolder" & "FAC" & "Payslips", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False






End Sub

The data on the index sheet is in the following format viz.



[TABLE="width: 150"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet_Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P004[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P005[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P243[/TD]
[TD]FAC[/TD]
[/TR]
[TR]
[TD]P244[/TD]
[TD]FAC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanx[/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 150"]
<colgroup><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Fluff]

I'm afraid I don't understand what you are asking for.
Can you please re-explain?

 

[riazd]

Hi

The index page has a list of locations and sheet names, my aim is to select & print all sheets associated with a specific location, so for location "VER" i need to print 003, 004, P004, P005, & for location "FAC" i would print P243 & P244, but i would like to achieve this by looping through the ranges. Currently i'm hard coding the location into the macro with the

If (p.Offset(, 1)) = "FAC"

But i want to loop through the locations instead, as they may change in the future,

Thanx for your interest in my question

 

[Dan5977]

An alternative idea for you as I don't know how to do it the way you are attempting. If I'm understanding this correctly, I think you could simplify this by having your location in a cell on each sheet. The location could change based on a formula if necessary.

You can then simply print sheets for the location using the following code (assuming location is in cell A1 on each sheet) linked to a command button. You could have a command button for each location.

Private Sub CommandButton1_Click()
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
    If WS.Range("$A$1") = "VER" Then WS.PrintOut
      Next WS
End Sub

 

[Fluff]

But i want to loop through the locations instead

Your code is already looping through the values in col A, so I'm still not sure I understand.

 

[riazd]

Hi Fluff

I want to extract the unique values in col b into a range, eg. "c" and than i want to loop through col b and where it is equal to a unique value from "c", it must collect the sheets into ShtGroup and than print it

Thanx

 

[Fluff]

In your op you said you have data like
[TABLE="class: cms_table, width: 150"]
<tbody>[TR]
[TD]Sheet_Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P004[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P005[/TD]
[TD]Ver[/TD]
[/TR]
[TR]
[TD]P243[/TD]
[TD]FAC[/TD]
[/TR]
[TR]
[TD]P244[/TD]
[TD]FAC[/TD]
[/TR]
</tbody>[/TABLE]

Is this correct? If so what columns is the data in?

 

[riazd]

Hi Fluff

My data is exactly like you have it, in Column A & B,

My question is if i create a range and assign all of the unique values from column B to it, how would i then write the code so that it loops through column b and where column b is equal to the value assign the sheet name to the Shtgroup to be used to print each unique value to a seperate pdf

Thanx again for assisting

 

[Fluff]

Are you saying that you don't always want to print all "Ver" & all "FAC" etc, but only print the "Ver" sheets?

 

[riazd]

Hi Fluff

I want to print all the sheets that are associated with the locations to different pdfs, my problem is if the locations increases i will have to manually add the new locations to the code so that they are not omitted, currently there are 12 locations, i've just provided a selection, as i thought the repetitive code would be an tedious.

I'm thinking i should create a unique list as a range of column B, with something like this,


Dim lastrow As Long
Dim rngUniques As Range



lastrow = Range("B:B").find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("B1:B" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUniques = Range("B2:B" & lastrow).SpecialCells(xlCellTypeVisible)
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData





End Sub

and then loop through this unique range, so for every value in the range if it equals the offset than add the sheet to the shtgroup,

Thanx for your continued assistance