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]

Debugs on:
Sheets(M.Cells(i, 2).Value).Select False

 

[sheetspread]

Hmmm, it worked when I tried it in preview mode and just selecting the sheets all at once. Did you rename the source sheet to Master?

 

[robbo0224]

I tried both, changing Master to the sheet name and changing the sheet name to master and both debug at the same point

 

[robbo0224]

The plot thickens, when it debugs and i right click and look at the definition, it says; 'Cannot jump to 'Sheets' because it's hidden'
I've double checked and there are no hidden sheets in the workbook, so i dont know why that's coming up

 

[sheetspread]

And I got an error when trying to preview again (???). Here's a code which passes the sheetnames to an array, how does that go?

Sub printhyperlinks()
Dim Week%, Col%, LastRow%, M As Worksheet, sheetarr(), i%, j%
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
j = 0
For i = 1 To LastRow
If M.Cells(i, Col).Value = "X" Then
ReDim Preserve sheetarr(j)
sheetarr(j) = M.Cells(i, 2).Value
j = j + 1
Else
End If
Next
Sheets(sheetarr).PrintOut Copies:=1, Collate:=True
End Sub

 

[robbo0224]

Debugs on the last line again. Could it have something to do with my printer?

 

[sheetspread]

if you change it to

Sheets(sheetarr).PrintPreview

do you still get errors?

 

[robbo0224]

Yes, debugs on the same line. Does the sheetarr in this instance need the (j) after it?

 

[sheetspread]

No I was able to preview with the whole array, no looping needed. Are you sure the cells in that column match the sheet names exactly?

 

[sheetspread]

Also are you still getting the hidden worksheet warning?