Array To View Specific Sheet(s)

Prev: Existing File Not Found
Next: Code to update links every 30 seconds

From: GEdwards on 25 Mar 2010 01:46

---

Using a macro I need to view specific worksheets if they have content. I
will always want my "Invoice" sheet and if on the Invoice sheet, cell B12 has
a value I want to view the Invoice and Labour sheet. If cell B13 has a value
I also want to view the Materials sheet.

My macro, shown below, halts at the line "Sheets(Array(prtWhat)).Select".

How can I dynamically build the proper statement...
"Sheets(Array("Invoice", "Labour", "Materials")).Select"

Please help.

Sub ViewTest()

prtWhat = "Invoice"
Labour = ", Labour"
Materials = ", Materials"
If Range("B12").Value <> "" Then
prtWhat = prtWhat & Labour
Else
prtWhat = prtWhat
End If
If Range("B13").Value <> "" Then
prtWhat = prtWhat & Materials
Else
prtWhat = prtWhat
End If

MsgBox (prtWhat)

Sheets(Array(prtWhat)).Select
Sheets("Invoice").Activate
ActiveWindow.SelectedSheets.PrintPreview
Sheets("Invoice").Select
End Sub

From: Rick Rothstein on 25 Mar 2010 02:14

---

Don't use the Array function, rather, use the Split function... its first
argument is a String which you can build dynamically. For example...

Dim SheetStr As String
...........
...........
SheetStr = "Invoice"
With Sheets(SheetStr)
If .Range("B12").Value <> "" Then SheetStr = SheetStr & ",Labour"
If .Range("B13").Value <> "" Then SheetStr = SheetStr & ",Materials"
End With
Sheets(Split(SheetStr, ",")).Select

Note the comma in front of the additional sheet names that are being
concatenated... that is the delimiter that is used in the Split function
call (don't add any spaces around those comma or else the spaces will end up
in the sheet names).

--
Rick (MVP - Excel)



"GEdwards" <GEdwards(a)discussions.microsoft.com> wrote in message
news:7C99D243-716F-4E18-AF5E-8C2948916A74(a)microsoft.com...
> Using a macro I need to view specific worksheets if they have content. I
> will always want my "Invoice" sheet and if on the Invoice sheet, cell B12
> has
> a value I want to view the Invoice and Labour sheet. If cell B13 has a
> value
> I also want to view the Materials sheet.
>
> My macro, shown below, halts at the line "Sheets(Array(prtWhat)).Select".
>
> How can I dynamically build the proper statement...
> "Sheets(Array("Invoice", "Labour", "Materials")).Select"
>
> Please help.
>
> Sub ViewTest()
>
> prtWhat = "Invoice"
> Labour = ", Labour"
> Materials = ", Materials"
> If Range("B12").Value <> "" Then
> prtWhat = prtWhat & Labour
> Else
> prtWhat = prtWhat
> End If
> If Range("B13").Value <> "" Then
> prtWhat = prtWhat & Materials
> Else
> prtWhat = prtWhat
> End If
>
> MsgBox (prtWhat)
>
> Sheets(Array(prtWhat)).Select
> Sheets("Invoice").Activate
> ActiveWindow.SelectedSheets.PrintPreview
> Sheets("Invoice").Select
> End Sub

From: Jacob Skaria on 25 Mar 2010 02:33

---

Try the below....It is always better to mention the sheet name while
referring to the range. Instead of Range("B12") mention as
Sheets("Sheetname").Range("B12")


Sub ViewTest()
Dim strSheets As String

strSheets = "Invoice"
If Range("B12") <> "" Then strSheets = strSheets & ",Labour"
If Range("B13") <> "" Then strSheets = strSheets & ",Materials"

Sheets(Split(strSheets, ",")).Select
Sheets("Invoice").Activate
ActiveWindow.SelectedSheets.PrintPreview
End Sub




--
Jacob


"GEdwards" wrote:

> Using a macro I need to view specific worksheets if they have content. I
> will always want my "Invoice" sheet and if on the Invoice sheet, cell B12 has
> a value I want to view the Invoice and Labour sheet. If cell B13 has a value
> I also want to view the Materials sheet.
>
> My macro, shown below, halts at the line "Sheets(Array(prtWhat)).Select".
>
> How can I dynamically build the proper statement...
> "Sheets(Array("Invoice", "Labour", "Materials")).Select"
>
> Please help.
>
> Sub ViewTest()
>
> prtWhat = "Invoice"
> Labour = ", Labour"
> Materials = ", Materials"
> If Range("B12").Value <> "" Then
> prtWhat = prtWhat & Labour
> Else
> prtWhat = prtWhat
> End If
> If Range("B13").Value <> "" Then
> prtWhat = prtWhat & Materials
> Else
> prtWhat = prtWhat
> End If
>
> MsgBox (prtWhat)
>
> Sheets(Array(prtWhat)).Select
> Sheets("Invoice").Activate
> ActiveWindow.SelectedSheets.PrintPreview
> Sheets("Invoice").Select
> End Sub

 | 
Pages: 1
Prev: Existing File Not Found
Next: Code to update links every 30 seconds