VBA excel 2010 working with sheets names and delete blanc sheets

Question

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

`Skip` isnt a keyword in VB. `If` needs a matching `End If`. Are you familiar with VBA? — shahkalpesh, Apr 06 '13 at 18:40
well, becouse VBA is filling in automaticly the capital letter "S" I thought VBA would recognize the process. (so I know about the basics studies I did. Althuogh I am just a student in progress. Managing all the knowledge that VBA can will take years of experiance and I am short of time at the moment.I am verry sorry for the inconveniance.. — user2151190, Apr 06 '13 at 18:59

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

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

edited May 23 '17 at 12:03

Community

1
1

answered Apr 06 '13 at 19:56

Siddharth Rout

147,039
17
206
250

Wel I must admitt that this looks like a verry constructive solution Mr Siddhart. Thanks for your quick response!!! For now I vote up your code and tomorrow morning I wil try to rewrite my code and keep you informed! I am verry excited to see it it is doing the right handling! Here it is 22:34h at the moment and I am a little burned out trying today.... I really appiciate your help :-). – user2151190 Apr 06 '13 at 20:36
@Mr Siddarth. formulazone = merge data. It is a little long for explaining by writing. is there any way to command: For Each ws In ThisWorkbook.Worksheets If ws.Name = "NAME SPECIFIC 1" Then 'formula merge data OR If ws.Name = "NVT1" Then abort process??? Could that be done right after the search command for each filename?? The code would probably be a bit slower but that is what it actually should do. We are goiing to delete blanc sheets in the new file ("map1") after finishing the merge process. Into a light version of workbook it is working well, but my workbook is a bit complexer. – user2151190 Apr 07 '13 at 09:22
Call me Siddharth or Sid :) I am not sure I understand your new request... `If ws.Name = "NAME SPECIFIC 1" Then 'formula merge data` – Siddharth Rout Apr 07 '13 at 09:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/27714/discussion-between-user2151190-and-siddharth-rout) – user2151190 Apr 07 '13 at 09:28

score 0 · Answer 2 · answered Apr 06 '13 at 18:54

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

VBA excel 2010 working with sheets names and delete blanc sheets

2 Answers2