VBA calling the name of a Sheet from a cell

[Gio_ita43]

Hi chaps,
I am a newbie.
I have a simple question:

I have a macro for printing a sheet whose name is in a cell

Sub printchartnew1()
' printchartnew1 Macro

Sheets("TF90").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub


Now in a separate sheet of the same workspace I have a list of the sheet names:
A
1 TF90
2 TF88
3 TF87
4 TF65
5 TF78
6 TF43

I want to create a macro which prints the sheet whose name is in the list. For example A2 for printing the Sheet labelled TF88.

Finally I would like a macro which prints ALL the sheets whose name are on that list.

I think I have to change something in here:

Sheets("TF90").Select

Actually in the macro I am selecting the sheet and then printing but I guess that the selection step can be skipped somehow....

Any help would be extremely appreciated.

 

[HalfAce]

Hi and welcome to the board.
Here are a couple things for you to try out.
The first one requires the user to select a cell in column A, and click the button to print just the sheet named in that cell:

Sub PrintThisSheetOnly()
If ActiveCell.Column <> 1 Then Exit Sub
If ActiveCell = "" Then MsgBox "You must select a non-blank cell.": Exit Sub
Application.ScreenUpdating = False
Sheets(ActiveCell.Value).PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Application.ScreenUpdating = True
End Sub

This second one will print all sheets listed in column A:

Sub PrintAllSheetsInTheList()
Dim ThisSheet As Range, LastSheet As Long
Application.ScreenUpdating = False
LastSheet = Cells(Rows.Count, "A").End(xlUp).Row
For Each ThisSheet In Range("A1:A" & LastSheet)
  Sheets(ThisSheet.Value).PrintOut Copies:=1, Collate:=True, _
  IgnorePrintAreas:=False
Next ThisSheet
Application.ScreenUpdating = True
End Sub

Are either of these close to what you're looking to do?

 

[Gio_ita43]

Hi HalfAce,
thank you very much for your reply.

Actually I have a sheet called

TF82_002 with my plot

and a Sheet called "Sheet5" where in column A there is the name of the sheets I want to print out. In the cell A1 there is
TF82_002

What I have done is creating a macro "PrintThisSheetOnly" where I copied your code. Then I clicked on the cell A1 of Sheet5 and Developer>Macros>(selected)PrintThisSheetOnly>run but I got an error message:

Run-time error '1004':

Application-defined or object-defined error

I clicked on Debug and I found highlighted:

Sheets(ActiveCell.Value).PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

with the yellow arrow pointing at IgnorePrint...

Ideally I would like to have a button where I click and run the macro.
I know I just go on Insert and insert a rectangular button, then right click and I attribute the macro to the button. I was just wandering if it works like that because you said that in your macro you have to click on the cell where the name of the Sheet is whereas in this case I would click on the button....

I am running excel 2010

Sorry I am really new, I am so grateful for your patience.

 

[HalfAce]

ok, first thing first.
I was getting the error you got also and figured it was just an excel version issue. (I'm only using 03 here). All I did was insert an apostrophe right before the comma in this line: (apostrophe is red)
Sheets(ActiveCell.Value).PrintOut Copies:=1, Collate:=True ', _
IgnorePrintAreas:=False

Now, for this: "I was just wandering if it works like that because you said that in your macro you have to click on the cell where the name of the Sheet is whereas in this case I would click on the button...."
The reason the user needs to click on a cell is not to fire off the code, but to give the routine a sheet name to work with. The sheet to print is the sheet named in the selected cell. The user still needs to click the button to execute the routine.

Does that help at all?

 

[Gio_ita43]

Half,
Thank you very much for your help. Those were the macros I was precisely looking for.

I wish you a life of happiness and prosperity!

 

[HalfAce]

You're very welcome Glad it helped.

As for your wish, (first of all), thank you. Secondly, it has been granted.

 

[Gio_ita43]

Hi Half,
I hope you are still around.

Sadly I am here again because I noticed another problem with my macros.
They work fine, but when I create the macro and save it, I got the following pop up window:

"The following features cannot be saved in macro-free workbooks:"
VB project
to save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes"

If I click Yes it will be saved.
The problem is that when I open up the file again, it does not contain any macro.

If I click No I get the pop up window that appears when you click on "Save as". So I try to save as Excel97-2003 but I got error messages saying that in some spreadsheets the data exceed the column limit (if I remember correctly it is 256).

I guess it is a problem related to the version of excel/File format.
I am using excel 2010.


Any thoughts about that?

Thank you very much.

 

[HalfAce]

Morning Gio,
Everything you said makes sense considering there is a version difference. I'm using 03 here, I have 07 at home and I've never seen 010 yet.
I'm assuming the error message means you have more than 256 columns being used in the workbook.
I've never used 010 so I don't know what might be there to account for something like that but I would have to think there's a way you can save it as a macro enabled wb in the 010 format. Is that not an option?

 

[Gio_ita43]

Good morning Half.
Thank you very much. Yes saving as macro enabled wb (second option in the drop-down list) solved the problem!