Excel - same print area on different worksheets?

  stubacka2 10:37 27 Jun 2006

I have been sent an Excel workbook that contains hundreds of worksheets that are identical layouts of a large questionnaire. The questionnaire has been completed by different customers on each sheet.
I need to print out the same portion of each sheet but not the complete sheet. Is it possible to set the same print area for each sheet at one go without having to manually set it in each individual sheet as this would take hours if not days to do?

  VoG II 10:41 27 Jun 2006

Click the first sheet tab. Hold down Shift and click the last Sheet tab. All sheets should now be selected and if you change the print area on any one it should change the lot. Clicking the print button should print all the sheets.

  GroupFC 10:58 27 Jun 2006

I bow to your superior knowledge but - I have just tried that and when I go to set the print area that option is greyed out!

I am using Excel 2000, if that makes any difference.

  VoG II 11:07 27 Jun 2006

Thanks GroupFC - I should have tried it first!

Try a macro:

Sub SetPA()
Dim wsht As Worksheet
For Each wsht In Worksheets
wsht.PageSetup.PrintArea = "$A$1:$N$31"
Next wsht
End Sub

change $A$1:$N$31 to your desired range.

Press ALT + F11 to open the Visual Basic Editor. Insert > Module. Paste in the above code. Close the VBE. Tools > Macro > Macros, click on SetPA and click the Run button.

  stubacka2 14:16 27 Jun 2006

Thanks Vog, the macro worked a treat.

I removed the PrintOut line for this exercise so that I could "Fit to One Page" (page layout with all sheets selected) before printing. Otherwise perfect.

Thanks again

  Zaphod 3 14:40 27 Jun 2006

Added to my postings could be handy for work.

Many thanks VOG

  Legolas 18:08 27 Jun 2006

I will be supervising a Data Cleanse at work soon and we use Excel to store all the info of what should and should not be deleted. This might be very handy for printing out data from multiple sheets.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Print designer Kelly Anna on confident mark making & modern femininity

New iMac Pro release date, UK price & specs rumours

Comment créer, utiliser et supprimer son compte Facebook ?