Macro VBA Run-Time Error 1004 --- Macro will execute twice flawlessly, then throws a 'RTE 1004' error.

RLB1127

New Member
Joined
Sep 14, 2016
Messages
11
I have a 'reset' macro that toggles a workbook view (hiding & unhiding worksheets). It runs great 2 times and then fails, but I don't have enough experience to understand why or fix it. There are (3) macros in my workbook: Reset, KView, BView. Any help appreciated.

Reset Macro is as follows:

Sub ResetButton()
'
' ResetButton Macro
'Sub clearslcr()
Dim slcr As SlicerCache
Dim sl As Slicer


For Each slcr In ActiveWorkbook.SlicerCaches
For Each sl In slcr.Slicers

slcr.ClearManualFilter

Next sl
Next slcr

Sheets("Item Distribution").Select
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Range("B8").Select
Sheets(Array("Item Distribution", "Item % Distribution")).Select
Sheets("Item Distribution").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("Distribution Grid Cover").Select
Range("B3:O3").Select
End Sub
Sub KView()
'
' KView Macro
'

'
Sheets("Distribution Grid Cover").Select
Sheets("Item Distribution").Visible = True
Sheets("Distribution Grid Cover").Select
Sheets("Item % Distribution").Visible = True
Sheets("Item Distribution").Select
Rows("1:6").Select
Range("B1").Activate
Selection.EntireRow.Hidden = True
Range("B8").Select
Sheets("Item % Distribution").Select
Range("B8").Select
Sheets("Item Distribution").Select
End Sub
Sub BView()
'
' BView Macro
'

'
Sheets("Distribution Grid Cover").Select
Sheets("Item Distribution").Visible = True
Sheets("Distribution Grid Cover").Select
Sheets("Item % Distribution").Visible = True
Sheets("Item Distribution").Select
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
Range("B8").Select
End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,988
Office Version
  1. 2019
Platform
  1. Windows
If you debug when it throws the error it will tell you which line is causing the problem.

Best guess would be that you're trying to select a sheet which is hidden.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,010
Messages
5,545,479
Members
410,686
Latest member
chandraganji
Top