parameter to open file name in macro
 

I am trying to write a macro to print many worksheets from many workbook.
However, each month some of the worksheet have different naming based on the
month. For example: "NOV_PJ" and the next month "DEC_PJ". is there a way to
have a parameter in the macro to ask for the file name before having the
macro print out the worksheet?

parameter to open file name in macro
 

Assuming you had your loop set up as:

For Each Wb In myFolder
For Each Ws In Wb
If Not Ws Is Nothing Then
shName = Ws.Name
MsgBox shName
Ws PrintOut
End If
Next
Next

Where the message box is inserted above you could use a
Yes/No criteria to determine if you want to print the
particular sheet and insert code that would skip to the next
sheet if print is not required.

Without seeing your actural code, I don't know if this is what
you were looking for, but it should give you an idea of how to
handle it.

"KrisN" wrote:

I am trying to write a macro to print many worksheets from many workbook.
However, each month some of the worksheet have different naming based on the
month. For example: "NOV_PJ" and the next month "DEC_PJ". is there a way to
have a parameter in the macro to ask for the file name before having the
macro print out the worksheet?

parameter to open file name in macro
 

I am still a little lost. I am new to macro in excel and I used the record
macro to create this the following code:

Sub Sales_Strategy_FX()

Workbooks.Open Filename:="G:\MARKET\PSI\COPIER\2007B PSI\Jan 07B FX.xlsx", _
UpdateLinks:=0
Sheets("Sales Flash").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Half to Date").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub

The path that list the file name Jan 07B FX.xlsx is the file that would
change each month...ergo next month the file name would be Feb 07B FX. This
is were I need the parameter or msgbox to ask for the file I want to print.
The yes/no message box to print and or skip to next sheet would also be a
plus and is beyond my knowledge. If you could help me with this code as well
I would greatly appreciate it.

"JLGWhiz" wrote:

Assuming you had your loop set up as:

For Each Wb In myFolder
For Each Ws In Wb
If Not Ws Is Nothing Then
shName = Ws.Name
MsgBox shName
Ws PrintOut
End If
Next
Next

Where the message box is inserted above you could use a
Yes/No criteria to determine if you want to print the
particular sheet and insert code that would skip to the next
sheet if print is not required.

Without seeing your actural code, I don't know if this is what
you were looking for, but it should give you an idea of how to
handle it.

"KrisN" wrote:

I am trying to write a macro to print many worksheets from many workbook.
However, each month some of the worksheet have different naming based on the
month. For example: "NOV_PJ" and the next month "DEC_PJ". is there a way to
have a parameter in the macro to ask for the file name before having the
macro print out the worksheet?