How To Group like sheets by name then print & loop

[adel7686]

Hi,

Below is my code that will loop through a workbook and print each worksheet individually.

Sub print_sheets()

Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
WS.Activate

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next WS

End Sub

I'm now trying to add code in order to find worksheets with the same account number in the sheet name and group them - then print, and move on the the next account... below is an example of 2 accounts (4 sheets) names I'm trying to work with:

Sheet Name examples:
<table border="0" cellpadding="0" cellspacing="0" height="102" width="175"><colgroup><col style="width:48pt" width="80"> </colgroup><tbody><tr style="height:12.75pt" height="21"> <td style="height:12.75pt;width:48pt" height="21" width="80">1885 Cash 05.31.11</td> </tr> <tr style="height:12.75pt" height="21"> <td style="height:12.75pt" height="21">1885 Holdings 05.31.11</td> </tr> <tr style="height:12.75pt" height="21"> <td style="height:12.75pt" height="21">10040 Cash 05.31.11</td> </tr> <tr style="height:12.75pt" height="21"> <td style="height:12.75pt" height="21">10040 Holdings 05.31.11</td> </tr> </tbody></table>
I'd like to be able to find all sheets with for ex. account "1885" and print them together, then move on to the next account and do the same. i.e. then find 10040 and print the cash/holdings sheets together. I've come up with the excel formula to extract the account number:

"=LEFT(A18,FIND(" ",A18,1)-1)"
or in vba something like:
"=LEFT("sheet name",FIND(" ","sheet name",1)-1)"
(this should be done using arrays... i think?)

I'd really appreciate any help ... I'm a self taught VBA nerd and I haven't been able to find anything quite like my issue.

Thanks in advance,

Anthony

 

[daverunt]

Hi,

here's a simple way of printing WS with a specific account number input by the user.

Sub print_sheets()
Dim WS As Worksheet
 
AccountToPrint = InputBox("Enter Number of account only i.e 10040")
 
For Each WS In ActiveWorkbook.Worksheets
WS.Activate
If InStr(WS.Name, AccountToPrint) > 0 Then
 
MsgBox (WS.Name) ' a visual check for testing
 
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
End If
Next WS

Alternatively you could set up an array of the account names and print the sheets corresponding to each array index consecutively

Sub print_sheets2()
Dim WS As Worksheet
 
'Array of Account names
arrAcc = Array("1885 Cash 05.31.11", "1885 Holdings 05.31.11", "10040 Cash 05.31.11", "10040 Holdings 05.31.11")
 
For Each WS In ActiveWorkbook.Worksheets
WS.Activate
 
For arrIndex = LBound(arrAcc) To UBound(arrAcc)
If InStr(WS.Name, arrAcc(arrIndex)) > 0 Then
 
MsgBox (WS.Name)
' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
End If
Next
 
Next WS
 
End Sub

 

[adel7686]

Thanks Dave!

The workbooks that I'm dealing with contain approx 100 accounts each (200 worksheets min. depending on the currencies that account holds (an account could have 5 cash tabs and 1 holding tabs). Is there a way to do this without a user prompt?

Anthony

 

[daverunt]

Currently the only way I can think to do it is using the second method I posted which uses an array of worksheet names without the need for an input box.

This will require some work to enter all the account names into the array though.

Anyone else think of another method?

 

[daverunt]

You could shorten the info in the array to just account numbers?

Sub print_sheets3()
Dim WS As Worksheet
 
'Array of Account numbers
 
arrAcc = Array("1885", "10040")
 
For Each WS In ActiveWorkbook.Worksheets
WS.Activate
 
For arrIndex = LBound(arrAcc) To UBound(arrAcc)
If InStr(WS.Name, arrAcc(arrIndex)) > 0 Then
 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
End If
Next
Next WS
End Sub