paposhisho
New member
- Joined
- Jul 29, 2016
- Messages
- 1
- Reaction score
- 0
- Points
- 0
Hello All,
I have a range of possible tab names listed in my PERSONAL.xlsb workbook. My code references that list of good names and deletes all other sheets if there is no match. The issue i'm having is that when i run the macro, everything works fine, except that there is sometimes a sheet leftover at the end of the macro. It is usually the last tab in the book as-if the macro isn't capturing or checking/matching all the tabs against the list.
Here is the code:
Any help or suggestions would be appreciated! I believe the error is occurring in the For i = Sheets.Count To 1 Step (-1) block.
Matt
I have a range of possible tab names listed in my PERSONAL.xlsb workbook. My code references that list of good names and deletes all other sheets if there is no match. The issue i'm having is that when i run the macro, everything works fine, except that there is sometimes a sheet leftover at the end of the macro. It is usually the last tab in the book as-if the macro isn't capturing or checking/matching all the tabs against the list.
Here is the code:
Code:
[INDENT]Sub GenerateSummary()
[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Dim ws As Worksheet[/INDENT]
[INDENT]Dim varResult As Variant
[/INDENT]
[INDENT]'Dim sht, sarray[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'displays the save file dialog[/INDENT]
[INDENT] varResult = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'checks to make sure the user hasn't canceled the dialog[/INDENT]
[INDENT] If varResult <> "False" Then[/INDENT]
[INDENT] ActiveWorkbook.SaveAs varResult[/INDENT]
[INDENT] End If[/INDENT]
[INDENT] If varResult = False Then[/INDENT]
[INDENT] MsgBox "User Cancelled"[/INDENT]
[INDENT] Exit Sub[/INDENT]
[INDENT] End If[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Application.ScreenUpdating = False[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'Copies and pastes values for all sheets except the Financial Summary[/INDENT]
[INDENT]'If you wish to leave formulas for other sheets besides Financial Summary then[/INDENT]
[INDENT]'after "Financial Summary" in the code below, add the following:[/INDENT]
[INDENT] 'AND ws.Name <> "name of ws you want to keep formulas in"[/INDENT]
[INDENT]For Each ws In Sheets[/INDENT]
[INDENT] If ws.Name <> "Financial Summary" Then[/INDENT]
[INDENT] ws.Activate[/INDENT]
[INDENT] Cells.Select[/INDENT]
[INDENT] With Selection[/INDENT]
[INDENT] .Copy[/INDENT]
[INDENT] .PasteSpecial xlPasteValues[/INDENT]
[INDENT] End With[/INDENT]
[INDENT] End If[/INDENT]
[INDENT]Next ws[/INDENT]
[INDENT]Application.CutCopyMode = False[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'Sets range as values listed in column A on the Personal.xlsb worksheet[/INDENT]
[INDENT]Dim TabsToKeep As Range[/INDENT]
[INDENT]Set TabsToKeep = Workbooks("Personal.xlsb").Sheets("Sheet1").Range("A:A")[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'Removes all tabs not listed in column A on the Personal.xlsb worksheet[/INDENT]
[INDENT]Dim i As Long, x, wsAct As Worksheet[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Set wsAct = ActiveSheet[/INDENT]
[INDENT]For i = Sheets.Count To 1 Step (-1)[/INDENT]
[INDENT] If Not Sheets(i) Is wsAct Then[/INDENT]
[INDENT] x = Application.Match(Sheets(i).Name, TabsToKeep, 0)[/INDENT]
[INDENT] If IsError(x) Then[/INDENT]
[INDENT] Application.DisplayAlerts = False[/INDENT]
[INDENT] Sheets(i).Delete[/INDENT]
[INDENT] Application.DisplayAlerts = True[/INDENT]
[INDENT] End If[/INDENT]
[INDENT] End If[/INDENT]
[INDENT]Next i[/INDENT]
[INDENT]
[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'Repositions the sheets to Cell A1[/INDENT]
[INDENT]For Each ws In Sheets[/INDENT]
[INDENT] If ws.Visible Then[/INDENT]
[INDENT] ws.Activate[/INDENT]
[INDENT] Range("A1").Select[/INDENT]
[INDENT] ActiveWindow.ScrollRow = 1[/INDENT]
[INDENT] ActiveWindow.ScrollColumn = 1[/INDENT]
[INDENT] End If[/INDENT]
[INDENT]Next ws[/INDENT]
[INDENT]
[/INDENT]
[INDENT]'Activates Financial Summary[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Worksheets("Financial Summary").Activate[/INDENT]
[INDENT]Application.ScreenUpdating = True[/INDENT]
[INDENT]
[/INDENT]
[INDENT]End Sub
[/INDENT]
Any help or suggestions would be appreciated! I believe the error is occurring in the For i = Sheets.Count To 1 Step (-1) block.
Matt
Last edited by a moderator: