VBA to Save as PDF broken with unwanted deleting of tabs.

[yearego021]

I have a macro to save the workbook into pdf that is currently "running" but deleting a sheet that I do not want deleted. Here are the steps of the macro.
1. Make the necessary tabs visible. - Works
2. Unprotect workbook. - Works
3. Create a copy of a tab for each varation of a value in a drop down list. - Works
4. Hide tabs not needed for pdf. - Works
5. Select all visible tabs. - Works
6. Save as pdf. - Works
7. Re-hide selected sheets. - Works (except for one sheet "Indirect Scorecard")
8. Delete all visible tabs. - Works (but deletes the sheet "Indirect Scorecard" that should be hidden because somehow it is still visible)
9. Unhide necessary tabs again. - Works
10. Reapply workbook protection. - Works
11. Error check, dialog box pdf confirmation. Works (except throws error because it can't unhide the deleted tab)
Essentially it works fine other than not hiding the "Indirect Scorecard" sheet before doing the delete all visible tabs step. Yet works fine for "Homepage" and "
Code below:

Sub Z_Button_Save_As_PDF()
rfdesw
Dim xRg As Range
Dim xCell As Range
Dim xRgVList As Range
Dim wKs As Worksheet
Dim wBa As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

Set xRg = Worksheets("Lender Scorecard").Range("B15")
Set xRgVList = Evaluate(xRg.Validation.Formula1)

Worksheets("Homepage").Visible = True
Worksheets("Indirect Scorecard").Visible = True
Worksheets("Manager Scorecard").Visible = True
Worksheets("Lender Scorecard").Visible = True

Application.DisplayAlerts = False

For Each wKs In ThisWorkbook.Worksheets

wKs.Unprotect "reporting"
Next

For Each xCell In xRgVList

xRg = xCell.Value

Worksheets("Lender Scorecard").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = xRg.Value
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Next

Worksheets("Lender Scorecard").Visible = xlHidden
Worksheets("Lender Trend").Visible = xlHidden
Worksheets("Lender Ranking").Visible = xlHidden

For Each wKs In ThisWorkbook.Worksheets

If wKs.Visible = xlSheetVisible Then
wKs.Select Replace:=False

End If

Next

On Error GoTo errHandler
Set wBa = ActiveWorkbook
Set wKs = ActiveSheet

'strTime = Format(Now(), "yyyymmdd\_hhmm")
'get active workbook folder, if saved
strPath = wBa.Path

If strPath = "" Then

strPath = Application.DefaultFilePath

End If

strPath = strPath & "\"
'replace spaces and periods in sheet name
strName = wBa.Name
strName = Replace(strName, ".xlsm", "")
'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'use can enter name and select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
'export to PDF if a folder was selected
If myFile <> "False" Then

wKs.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile

End If

Worksheets("Homepage").Visible = xlHidden
Worksheets("Indirect Scorecard").Visible = xlHidden
Worksheets("Manager Scorecard").Visible = xlHidden

For Each wKs In ThisWorkbook.Worksheets

If wKs.Visible = xlSheetVisible Then
wKs.Delete

End If

Worksheets("Homepage").Visible = True
Worksheets("Indirect Scorecard").Visible = True
Worksheets("Manager Scorecard").Visible = True
Worksheets("Lender Scorecard").Visible = True
Worksheets("Lender Trend").Visible = True
Worksheets("Lender Ranking").Visible = True


Next

For Each wKs In ThisWorkbook.Worksheets

wKs.Protect "reporting"

Next
exitHandler:
Exit Sub

errHandler:
MsgBox "Could not create PDF file"

Resume exitHandler


End Sub

 

[Artik]

Pay attention when hiding sheets. You can't hide them all. One must always be visible. This error in program logic is causing your probelms.

BTW. Don't use a thread title like “HELP.” It is obvious that anyone who asks a question on the forum needs help. So “HELP” is redundant code.

Artik

 

[yearego021]

Pay attention when hiding sheets. You can't hide them all. One must always be visible. This error in program logic is causing your probelms.

BTW. Don't use a thread title like “HELP.” It is obvious that anyone who asks a question on the forum needs help. So “HELP” is redundant code.

Artik

Thanks, so I need to add an exception to my delete visible sheets if. Tried that but not sure what I'm doing wrong here.

For Each wKs In ThisWorkbook.Worksheets

If wKs.Visible = xlSheetVisible And wKs.Name<>"Homepage" Then
wKs.Delete

End If

 

[Artik]

I think you're getting too much of a combination. As I understand it, you want to delete copies of the sheets you created in the first part of the macro. Using a similar loop as you named the new sheets, delete those sheets.

Artik

 

[yearego021]

I think you're getting too much of a combination. As I understand it, you want to delete copies of the sheets you created in the first part of the macro. Using a similar loop as you named the new sheets, delete those sheets.

Artik

Alright I'm lost, I get what your saying but don't know how to implement. Basically, For Each sheet with sheet name from my drop down list, delete. I don't know how to get it to check each value in the list against the sheet names.

 

[yearego021]

Alright I'm lost, I get what your saying but don't know how to implement. Basically, For Each sheet with sheet name from my drop down list, delete. I don't know how to get it to check each value in the list against the sheet names.

Update. Finally figured it out. Thanks for the help.

For Each xCell In xRgVList

xRg = xCell.Value

For Each wKs In ThisWorkbook.Worksheets

If wKs.Name = xRg.Value Then
wKs.Delete

End If

 

[Artik]

There is no need for two loops. One is enough:

    Application.DisplayAlerts = False
    
    For Each xCell In xRgVList
        ThisWorkbook.Worksheets(xCell.Value).Delete
    Next xCell
    
    Application.DisplayAlerts = True

Artik

 

[Artik]

Rather, a safer one would be this loop:

    Application.DisplayAlerts = False
   
    For Each xCell In xRgVList
        ThisWorkbook.Worksheets(CStr(xCell.Value)).Delete
    Next xCell
   
    Application.DisplayAlerts = True

If the cells in the validation list range contained numeric values, the preceding code would refer to the indexes of the sheets, not their names.