Hello-

I searched around for awhile, but becuase I m not sure exactly what to look
for, I could not figure out a way to do this...

I am using the following macro to create PDFs of sheets in a book.

Dim s As String
s = ActiveCell.Worksheet.Name
ActiveWorkbook.saveAs Filename:=s

Application.Run "EblastMacro2.xls!printAsPDF"

ActiveSheet.Next.Select

s = ActiveCell.Worksheet.Name
ActiveWorkbook.saveAs Filename:=s

Application.Run "EblastMacro2.xls!printAsPDF"

ActiveSheet.Next.Select

How can I write an If statement that will search for the next sheet to run
the macro on, but if there isn't a next sheet, then stop the macro. Right now
it will work fine, but instead of just stopping, it will give me runtime
error message.

Any thoughts?

Thank you,
BBkixx

dim i as integer

for i = 1 to activeworkbook.sheets.count step 1

' your code

next i


"bbkixx" wrote:

> Hello-
>
> I searched around for awhile, but becuase I m not sure exactly what to look
> for, I could not figure out a way to do this...
>
> I am using the following macro to create PDFs of sheets in a book.
>
> Dim s As String
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> How can I write an If statement that will search for the next sheet to run
> the macro on, but if there isn't a next sheet, then stop the macro. Right now
> it will work fine, but instead of just stopping, it will give me runtime
> error message.
>
> Any thoughts?
>
> Thank you,
> BBkixx

Sub what_ever()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
''''''''''''''''''''''''''''
'
' your macro here
'
''''''''''''''''''''''''''''
Next
End Sub


in place of the ActiveSheet.Next stuff
--
Gary''s Student


"bbkixx" wrote:

> Hello-
>
> I searched around for awhile, but becuase I m not sure exactly what to look
> for, I could not figure out a way to do this...
>
> I am using the following macro to create PDFs of sheets in a book.
>
> Dim s As String
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> How can I write an If statement that will search for the next sheet to run
> the macro on, but if there isn't a next sheet, then stop the macro. Right now
> it will work fine, but instead of just stopping, it will give me runtime
> error message.
>
> Any thoughts?
>
> Thank you,
> BBkixx

Might want to consider the following:

Public Sub WritePagesToPDF()

Dim aWS as Worksheet
Dim aWB as Workbook
Dim SheetName as String

Set aWB = ActiveWorkbook

Foreach aWS in aWB
aWS.Select
SheetName = aWS.Name
aWB.SaveAs SheetName

On Error Resume Next
Application.Run "EblastMacro2.xls!printAsPDF"

Next aWS


End Sub


The 'On Error' statement will avoid the runtime bug you mention ...

Hope this helps,
Chad


"bbkixx" wrote:

> Hello-
>
> I searched around for awhile, but becuase I m not sure exactly what to look
> for, I could not figure out a way to do this...
>
> I am using the following macro to create PDFs of sheets in a book.
>
> Dim s As String
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> s = ActiveCell.Worksheet.Name
> ActiveWorkbook.saveAs Filename:=s
>
> Application.Run "EblastMacro2.xls!printAsPDF"
>
> ActiveSheet.Next.Select
>
> How can I write an If statement that will search for the next sheet to run
> the macro on, but if there isn't a next sheet, then stop the macro. Right now
> it will work fine, but instead of just stopping, it will give me runtime
> error message.
>
> Any thoughts?
>
> Thank you,
> BBkixx