|  | Selecting sheets starting with a certain prefix (VBA) | | |
| | | Zark3 |  |
|  Posted: Wed Jul 16, 2008 11:22 am Post subject: Selecting sheets starting with a certain prefix (VBA) |  |
Hello,
Is there any way to use a wildcard or something in VBA when selecting
sheets? What I'm trying to accomplish is to print all sheets that
share a certain prefix. For example, my workbook contains sheets
123.a, 123.b, 123.c and Total. Now, I need to do something like
MyWorkbook.Sheets(Array("123.a", "123.b", "123.c")).Select:
MyWorkbook.SelectedSheets.PrintOut but of course without manually
specifying all possibilities but rather something like
MyWorkbook.Sheets("123.*").Select (which of course doesn't work but
illustrates what I want).
Does anybody have any suggestions?
Best,
Chris |
| |
| | | Bernie Deitrick | |
| | Posted: Wed Jul 16, 2008 11:23 am Post subject: Re: Selecting sheets starting with a certain prefix (VBA) | |
Chris,
Sub TryNow()
Dim mySh As Worksheet
For Each mySh In Worksheets
If mySh.Name Like "123*" Then mySh.PrintOut
Next mySh
End Sub
--
HTH,
Bernie
MS Excel MVP
"Zark3" <Zark3net@gmail.com> wrote in message
news:544c05ad-ed16-4c03-9161-769f7accecf0@c58g2000hsc.googlegroups.com...
| Quote: | Hello,
Is there any way to use a wildcard or something in VBA when selecting
sheets? What I'm trying to accomplish is to print all sheets that
share a certain prefix. For example, my workbook contains sheets
123.a, 123.b, 123.c and Total. Now, I need to do something like
MyWorkbook.Sheets(Array("123.a", "123.b", "123.c")).Select:
MyWorkbook.SelectedSheets.PrintOut but of course without manually
specifying all possibilities but rather something like
MyWorkbook.Sheets("123.*").Select (which of course doesn't work but
illustrates what I want).
Does anybody have any suggestions?
Best,
Chris |
|
| |
| | | Zark3 | |
| | Posted: Wed Jul 16, 2008 12:06 pm Post subject: Re: Selecting sheets starting with a certain prefix (VBA) | |
Thanks Bernie, a quick look with dummy data (running the full report
takes a couple of hours) and your solution seems to work like a charm!
For whom it may help, my code is:
'Save all worksheets to PDF
Dim rBuCell As Range: For Each rBuCell In rBuNumbers.Cells
'rBuNumbers is a named range holding [123][456][789] etc.
Dim ws As Worksheet: For Each ws In xlApp.Worksheets
If ws.Name Like rBuCell.Value & "*" Then 'Worksheets are
named 123.a, 123.b, 456.qq etc.
Dim sFilename: sFilename = sPathReport & "\" &
"Kostenkaart " & rBuCell.Value & " " & sPeriodLabel
ws.PrintOut ActivePrinter:=sPdfPrinter,
PrintToFile:=True, PrToFileName:=sFilename & ".ps"
oDist.FileToPDF sFilename & ".ps", sFilename & ".pdf",
""
Kill sFilename & ".ps"
Kill sFilename & ".log"
End If
Next ws
Next rBuCell |
| |
|
|
