Select sheet based on value in cell

[Hatye]

Hi,

I got an workbook I want to save as pdf. I want the pdf to contain multiple sheets, and that can be done by selecting each sheet and then save it as pdf. The problem is that I don't know which sheet to included. That is because I want to see if it is contents in cell A7, and if so: include that sheet in the selecting-properties.

You can use this code to select sheets:

Sheets(Array("Rapport", Rapport_Punkt)).Select

I want to see if any of the sheets "Rapport_Linjer", "Rapport_Punkt", "Rapport_Polygon", "Rapport_Tekst" and "Rapport_Triangelnett" got contents in the cell A7, and if so: include the sheet in the save as pdf function.

Please let me know if you want to see more of the code, to get the context.

 

[Trebor76]

Hi Hatye,

This will only select those sheets in the array that have something in cell A7:

Option Explicit
Sub Macro1()

    Dim varMyArray() As Variant 'Declares a dynamic array variable
    Dim lngArrayCount As Long
    Dim varMySheet As Variant
        
    For Each varMySheet In Array("Rapport_Linjer", "Rapport_Punkt", "Rapport_Polygon", "Rapport_Tekst", "Rapport_Triangelnett")
        If Len(Sheets(varMySheet).Range("A7")) > 0 Then
            lngArrayCount = lngArrayCount + 1
            ReDim Preserve varMyArray(1 To lngArrayCount) 'Append the record to the existing array
            varMyArray(lngArrayCount) = varMySheet
        End If
    Next varMySheet
    
    Sheets(varMyArray).Select
    
    Erase varMyArray
    
End Sub

HTH

Robert

 

[Hatye]

Thank you so much for your reply Trebor76,

It seems like it is working, but I forgot to write that I also need to insert the sheet "Rapport" at all time.

Tried the following

Sheets(array("Rapport"), varMyArray).Select

but that didn't work. I got an compile error.

Do you know how to fix that?

 

[Trebor76]

You need to add the sheet name in the first array like so:

For Each varMySheet In Array("Rapport", "Rapport_Linjer", "Rapport_Punkt", "Rapport_Polygon", "Rapport_Tekst", "Rapport_Triangelnett")

 

[Hatye]

But the sheet "Rapport" doesn't contain any data in the cell A7. It is a sort of first page on an report that always should be included.

 

[Trebor76]

Then as well as adding Rapport to the original array as above, put this line of code directly beneath it:

If Len(Sheets(varMySheet).Range("A7")) > 0 Or varMySheet = "Rapport" Then