Print specific Tab with Macros

[Uncle heFTy]

Now this may seem strange (I'm a 62 year old with good general Excel experience), but I have never worked with or produced a macro before, or worked with Visual Basic and it looks very daunting.

I am creating an order input sheet that links to sixteen tabs in the workbook, each referring to a line on the input sheet and which represent individual packing lists. My problem is that I'd like to be able to print a page from each of the sixteen tabs if, say, line 1 on the input sheet has a value, then to print tab 1 (does that make sense?)

My problem is knowing where to start - a bit like a sculptor trying to create a masterpiece from a block of granite, where do you start?

I've read Microsoft Excel 'Inside Out' and remain as resolutely daunted as when I began this task.

Can any kind soul out there help?

Ta
Uncle H

[royUK]

I would say the first thing to do is to make sure that your workbook design is best suited for the purpose. Is it necessary for different tabs?

Then try attaching an example workbook

[Uncle heFTy]

Thanks RoyUK and anyone else who might respond

I'm attaching a test workbook (hope that works) which shows the initial input sheet (LRS001) and each of the subsidiary Packing Sheets PAS001 - PAS016. What I a trying to do is to input data into cells C12 to C27 on LRS001, which then populates the rest of that sheet and creates an individual print for each of the PAS sheets - I have defined the print area for each.

As I said in my initial mail, this appears to be so daunting to me as to almost give up, but I know that someone will be able to help.

The Workbook is formatted for standard XLS but created in Excel 2011 for Mac OSX.

Thanks in advance.
Uncle heFTy

[royUK]

Your lookup formulas aren't working. What is supposed to be entered into C12?

[Uncle heFTy]

Your lookup formulas aren't working. What is supposed to be entered into C12?

The sample Catalogue Numbers that appear in cells N12 through N27 should work, the lookups refer to the tab 'Sheet 2'. They work for me OK.

Uncle H

[royUK]

I've added some code to print the sheets based on entries in the input sheet. It's currently set to printPreview for testing, simply change to this when you hare ready to use

Option Explicit

Sub PrintShts()
Dim rCl As Range

For Each rCl In Sheet1.Range("c12:c27")
If Not IsEmpty(rCl) Then Sheets(rCl.Row - 10).PrintOut
Next rCl
End Sub

I'm not sure if it will work with a Mac & the sheets should not be re-arranged . Their position in the workbook determines which sheet is printed

[Uncle heFTy]

I've added some code to print the sheets based on entries in the input sheet. It's currently set to printPreview for testing, simply change to this when you hare ready to use

Option Explicit

Sub PrintShts()
Dim rCl As Range

For Each rCl In Sheet1.Range("c12:c27")
If Not IsEmpty(rCl) Then Sheets(rCl.Row - 10).PrintOut
Next rCl
End Sub

I'm not sure if it will work with a Mac & the sheets should not be re-arranged . Their position in the workbook determines which sheet is printed

Thanks RoyUK, I tried it and changed it to printout and it worked!!!!

Happy and prosperous new year to you.
Uncle H

[royUK]

A good 2011 to you too

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save