How To Group like sheets by name then print & loop

adel7686

New Member
Joined
Jun 8, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Hi,

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

Code:
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

Code:
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
 
Last edited:

adel7686

New Member
Joined
Jun 8, 2011
Messages
2
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
You could shorten the info in the array to just account numbers?
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,955
Members
416,002
Latest member
t10k14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top