print specific sheets based on range into column

0

hi

I need  helping  to  fix  this  error  "type mismatch "in this line 

PrintArray = Split(inRange, ",")
   it  should  print  specific  sheets  are  existed  in  COL P in sheet MASTER  and  range  name's "ALLSHEETS"

thanks

Answer
Discuss

Answers

0
Selected Answer

Leopard

The VBA function SPLIT converts a string into an array (using a separator, like a comma) but you're trying to split a range (so you get an error).

You can get a 1-D array from a vertical range using TRANSPOSE instead (see bold like below) but your named range ALLSHEETS contains many blank entries (so I added a check on that (also in bold) in the revised code below since Excel won't actually find sheets with the user name "" and so fail:

Sub newprint()

Dim PrintArray As Variant, I As Integer

PrintArray = Application.Transpose(Range("ALLSHEETS"))

    For I = LBound(PrintArray) To UBound(PrintArray)
        If PrintArray(I) <> "" Then
            Sheets(PrintArray(I)).PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        End If
    Next

End Sub
Hope this fixes things for you (I haven't tried the print)
Discuss

Discussion

Hi John,
Thanks ,  it  works  greatly !
leopard (rep: 56) Jun 10, '21 at 4:11 am
Cheers Leopard. 
John_Ru (rep: 1267) Jun 10, '21 at 5:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login