VBA calling the name of a Sheet from a cell

Gio_ita43

New Member
Joined
Nov 3, 2011
Messages
5
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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:
Code:
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:
Code:
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?
 
Last edited:

Gio_ita43

New Member
Joined
Nov 3, 2011
Messages
5
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Nov 3, 2011
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
You're very welcome Glad it helped.

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

Gio_ita43

New Member
Joined
Nov 3, 2011
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Nov 3, 2011
Messages
5
Good morning Half.
Thank you very much. Yes saving as macro enabled wb (second option in the drop-down list) solved the problem!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,127
Messages
5,628,860
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top