generalizing macros; problem with multiple workbooks

[virgilgarcia]

Hi all,

I currently have a macro that is applicable to the current workbook. Is there a way for me to generalize this macro so that I can access it and make it work from practically any workbook by using the same shortcut key (perhaps storing it in the "Personal" file)?

the macro looks like this: (Credits to Tom Urtis)

Sub PrintRelevant()
Dim cell As Range
For Each cell In Sheets("Print").Columns(1).SpecialCells(2)
Sheets(cell.Value).PrintOut
Next cell
End Sub

The above macro prints the sheets whose names are stored in column 1 of the "Print" tab.

One issue that I encountered is that if I have multiple workbooks open at the same time, the macro is activated for all open workbooks. Is there a way for me to modify this macro so that it would only activate for the currently open workbook only?

Thanks in advance,

Virgil

 

[Trevor G]

Store your macro in the Personal Workbook, this allows you to use the macro on any workbook.

Add a line of code to use the activeworkbook

 

[virgilgarcia]

Hi Trevor,

Thanks for the reply.

I now have the following working macro that prints to PDF according to cell content:


Sub PrintRelevant()
Dim cell As Range
Application.ActivePrinter = "Adobe PDF on Ne02:"
For Each cell In ActiveWorkbook.Sheets("Print").Columns(1).SpecialCells(2)
ActiveWorkbook.Sheets(cell.Value).PrintOut

Next cell
End Sub



Now, is there a way for me to improve this such that instead of manually entering the filenames of the resulting PDF files, the macro would simply save it according cell contents from a different column?

In other words, this is what I have in mind:

(From the "Print" tab; column A contains hypothetical tab names and column B containts the desired filenames)

A B
apple apple charts
orange orange exhibits
coffee coffee graphs


So the macro would print and save to PDF in this manner:
1.) Print tab "apple" and save as "apple charts.pdf"
2.) Print tab "orange" and save as "orange exhibits.pdf"
3.) Print tab "coffee" and save as "coffee graphs.pdf"


Any thoughts?

Thanks a lot everyone,

Virgil

 

[virgilgarcia]

Can I follow up on this?

I'm still stuck, unfortunately. I've seen other threads online talking about problems similar to mine. Has anyone solved this yet?

 

[tusharm]

It should not work with all open workbooks. When there are unqualified references to Sheets() VBA / Excel assumes they are to the active workbook.

But, you could clarify the issue by using ActiveWorkbook.Sheets(...)

Hi all,

I currently have a macro that is applicable to the current workbook. Is there a way for me to generalize this macro so that I can access it and make it work from practically any workbook by using the same shortcut key (perhaps storing it in the "Personal" file)?

the macro looks like this: (Credits to Tom Urtis)

Sub PrintRelevant()
Dim cell As Range
For Each cell In Sheets("Print").Columns(1).SpecialCells(2)
Sheets(cell.Value).PrintOut
Next cell
End Sub

The above macro prints the sheets whose names are stored in column 1 of the "Print" tab.

One issue that I encountered is that if I have multiple workbooks open at the same time, the macro is activated for all open workbooks. Is there a way for me to modify this macro so that it would only activate for the currently open workbook only?

Thanks in advance,

Virgil

 

[tusharm]

You may want to look at **** Kusleika's suggestions about which product to use for VBA and PDF work.

http://www.dailydoseofexcel.com/archives/2011/01/05/how-i-do/

Hi Trevor,

Thanks for the reply.

I now have the following working macro that prints to PDF according to cell content:


Sub PrintRelevant()
Dim cell As Range
Application.ActivePrinter = "Adobe PDF on Ne02:"
For Each cell In ActiveWorkbook.Sheets("Print").Columns(1).SpecialCells(2)
ActiveWorkbook.Sheets(cell.Value).PrintOut

Next cell
End Sub



Now, is there a way for me to improve this such that instead of manually entering the filenames of the resulting PDF files, the macro would simply save it according cell contents from a different column?

In other words, this is what I have in mind:

(From the "Print" tab; column A contains hypothetical tab names and column B containts the desired filenames)

A B
apple apple charts
orange orange exhibits
coffee coffee graphs


So the macro would print and save to PDF in this manner:
1.) Print tab "apple" and save as "apple charts.pdf"
2.) Print tab "orange" and save as "orange exhibits.pdf"
3.) Print tab "coffee" and save as "coffee graphs.pdf"


Any thoughts?

Thanks a lot everyone,

Virgil

 

[virgilgarcia]

It should not work with all open workbooks. When there are unqualified references to Sheets() VBA / Excel assumes they are to the active workbook.

But, you could clarify the issue by using ActiveWorkbook.Sheets(...)

Hi Tushar,

I've already solved that problem (unfortunately I did encounter it) using exactly the same method as you prescribed. Anyway, I'm going to start reading up on the thread that you advised. I'll keep you posted.

Thanks for your help.

Virgil