printing according to worksheet name

[virgilgarcia]

Hi all,

I am totally new to this forum. I've read some of the other threads, and I must say there are quite a lot of proficient excel users here. Hopefully I can be able to contribute my own thoughts in the future as well.

Calling all experts, I really hope you can help me on this problem of mine (at least it's a problem for me. I have minimal background in VBA).

I'm need a macro that can print based on worksheet name. This is the idea that I have in mind:

In the first sheet, there will be an area wherein I can simply type (and save) the names of the worksheets that I would like to print. And then, the macro will scan this area and then print only the relevant worksheets based on name.

For example, my workbook has the following sheets:
A
B
C
D
E
F
G
H

In sheet A, I will type this in a certain area:
B
C
F
H

This way, the macro will scan the said area an print only the sheets with names that are stored in sheet A.

Any helpful comments and/or suggestions would be sincerely appreciated!

Regards,
Virgil

 

[Tom Urtis]

Assuming all you have in column A of sheet "A" is the list of sheet names, this would do what you want:


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

 

[virgilgarcia]

Thanks Tom.

How do I modify this so that I can scan column X of sheet Y?

Thanks,
Virgil

 

[Tom Urtis]

Sub PrintSheetzAgain()
Dim cell As Range
For Each cell In Sheets("Y").Columns(24).SpecialCells(2)
Sheets(cell.Value).PrintOut
Next cell
End Sub <!-- / message --><!-- sig -->

 

[virgilgarcia]

I have another question.

Is it possible for me to consolidate this, say, by storing this macro in the "Personal" workbook and simply access this one everytime for different files?

 

[Tom Urtis]

If it were me, I'd do it as an add-in rather than the PMW.

 

[virgilgarcia]

I'd like to turn this macro into a more centralized one, meaning I can access it from any workbook that is being currently active. And then the macro would scan the said column of the said tab (all of the workbooks would contain this tab, but these individual tabs would contain different names). Is there a way for me to do this?

 

[Tom Urtis]

As I suggested, I'd opt for an add-in. Steps slightly vary depending on the version of Excel you are using, so if you don't know how to set up an add-in, post back.

 

[virgilgarcia]

Hi Tom,

I guess I failed to read your previous post.

If you may, could you please expound on setting up an add-in?

Thanks in advance,

Virgil

 

[Tom Urtis]

I guess I failed to read your previous post.

I think you also failed to read my last previous post too.

Please say what version of Excel you are using.