Lookup set value, with multiple results.

[robbo0224]

Hi all,

I need help with a project i'm working on.
I have a sheet where I need to find and print worksheets based on weather that PPM is needed to be completed that month.
So for example, to Print all ppm's to be completed in week 6. I'd enter week 6 in the box and it looks up this week number in a row, then finds all occurrences of the number '1' in the column (there will be multiple) then look left to find the hyperlink to the sheet and print.

I need help with the lookup and looping it to find every entry.
Any help would be awesome. I'd attach the workbook, but i don't know how

 

[robbo0224]

This works, but only for the first sheet/item.
Then it throws up a debug.
Script out of range.
for the Sheets(Cells(i, 2).Value).PrintOut line.
It's a brilliant step forward, thank you

 

[sheetspread]

Maybe

Sheets(Cells(i, 2).Value).activate
activesheet.printout

(?)


wait where are these sheets you're printing? Different workbooks?

 

[robbo0224]

Yes these are in the same workbook.
It printed the first sheet, then threw up the debug which doesn't make sense to me.
Surely if the code is wrong shouldn't it debug before printing the first area?

 

[sheetspread]

No this happens all the time when switching between different sheets, I just have to remember how to fix it

 

[sheetspread]

I'm not connected to a printer now so it's hard to test, maybe a select/selection or activate/activesheet is needed (?)

 

[robbo0224]

I've just tried the adjust code and that works without the debug, but only print's the first area.
Is it something to do with the looping through all results?

 

[sheetspread]

Yes, but I can confirm it goes to each sheet. Show me your code.

Is your code in a module or sheet?

 

[robbo0224]

It's in a module.

Sub printhyperlinks()
Dim Week%, Col%, LastRow%
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Week = InputBox("Please enter Week number:")
Col = Application.Match("Week " & Week, Range("C1:J1"), 0) + 2
For i = 1 To LastRow
If Cells(i, Col).Value = "X" Then
Sheets(Cells(i, 2).Value).Activate
ActiveSheet.PrintOut
End If
Next
End Sub

 

[sheetspread]

Wait I think you have to include the master sheet name, hang on..........

 

[sheetspread]

Maybe not, but I changed that and another line, try:

Sub printhyperlinks()
Dim Week%, Col%, LastRow%, M As Worksheet
Set M = Sheets("Master")
LastRow = M.Cells(Rows.Count, 2).End(xlUp).Row
Week = InputBox("Please enter Week number:")
Col = Application.Match("Week " & Week, M.Range("C1:J1"), 0) + 2
For i = 1 To LastRow
If M.Cells(i, Col).Value = "X" Then
Sheets(M.Cells(i, 2).Value).Select False
End If
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

(the worksheet with the weeks and area should be renamed Master for this code to work)