CODEWITHSUNDEEP
vbaexcel
user2151190
user2151190

Reputation: 189

VBA excel 2010 working with sheets names and delete blanc sheets

I would like to know why VBA is telling me that the SUB function is missing while trying to write this code. The purpose should be that when the sheet is called NVT the code should skip any operation and go to the next sheet that will be activated (in the next command). In the end of this operation I should delete every blanc sheet(s) where there is no "specific name" or "NVT" filled in. The formula is working good without this option. I have no problem saving this code and no problem with the formula itselve. Any suggestion is welcom.. I don't believe this threat has been posted yet. Please let me know if you need additional information. The original code is verry long and would like just a indication how to sove this issue.Thanx in advace for who will answer tis threat.

Sub Printtabs()
' Print
ThisWorkbook.Activate
If ThisWorkbook.Sheets(7) = ("NVT") Then Skip
If ThisWorkbook.Sheets(7) = ("NAME SPECIFIC 1") Then
'process formula
End If
If Thisworkbook.Sheets (8) = ("NVT) Then Skip
If Thisworkbook.Sheets (8) = ("NAME SPECIFIC 2") Then
'process formula
End If
'then I should find the way to delete every "blanc" sheets in this workbook (becouse I skipped before and there will be blanc sheets) and save
End Sub

Upvotes: 1

Views: 2040

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

You don't need to use .Activate. You can directly work with the sheets. Also when deleting sheets and switching off events, always use proper error handling.

Is this what you are trying?

Dim ws As Worksheet

Sub Printtabs()
    On Error GoTo Whoa

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "NAME SPECIFIC 1" Then
            '~~> Process Formula
        ElseIf ws.Name = "NAME SPECIFIC 2" Then
            '~~> Process Formula
        Else
            If ws.Name <> "NTV" And WorksheetFunction.CountA(ws.Cells) = 0 Then
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        End If
    Next ws

LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Upvotes: 2

user2151190
user2151190

Reputation: 189

So, I figured out how to delete the blanc sheets I believe. Only the issue with sheetsnames is remaining. This part of code I will run at the end of all processed formulas. Hopely somebody could help me out.... 

Dim ws As Worksheet
For Each ws In Worksheets
    If WorksheetFunction.CountA(ws.Cells) = 0 Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
Next ws

Upvotes: 0

Related Questions