Run-time error '1004' select method of worksheet class failed - when hiding a worksheet

F

fkapyela

New Member
Joined
Feb 24, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have hidden 3 worksheets, and just after hidding I am not able to submit entry.
When I debug the error is below:
Sub TrainingDatabaseFinal2()
'
' TrainingDatabaseFinal2 Macro
' This macro submits and deletes data
'

'
Sheets("Intermediate").Select
Range("A2:Q2").Select
Selection.Copy
Sheets("DataBase").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("R3").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("Table1[[#Headers],[Training Type]]").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=-6
ActiveWindow.SmallScroll Down:=-3
ActiveCell.Offset(1, 0).Range("Table1[#Headers]").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data Entry").Select
ActiveWindow.SmallScroll Down:=21
Range("G33").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D33").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-6
Range("M25").Select
Selection.ClearContents
Range("J25").Select
Selection.ClearContents
Range("G25").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("M23").Select
Selection.ClearContents
Range("J23").Select
Selection.ClearContents
Range("G23").Select
Selection.ClearContents
Range("D23").Select
Selection.ClearContents
Range("D21").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-12
Range("G12").Select
Selection.ClearContents
Range("D12").Select
Selection.ClearContents
Range("M10").Select
Selection.ClearContents
Range("J10").Select
Selection.ClearContents
Range("G10").Select
Selection.ClearContents
Range("D10").Select
Selection.ClearContents
End Sub
 

Attachments

  • excel.JPG
    excel.JPG
    56.4 KB · Views: 34

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Welcome to the Board!

You cannot select ranges on a hidden sheet. You may need to temporarily unhide it at the beginning of your code, do your steps, and then re-hide it again at the end of the code.

Note that your code can be reduced significantly (it looks like you got a lot of your code using the Macro Recorder, which often can be cleaned up).

For example, it records all of your screen scrolling, lines that look like this:
VBA Code: 
ActiveWindow.ScrollColumn = 2
You can delete all of those, as those are not necessary for the VBA code to run and just gum it up.

Also, it is usually not necessary to select a range before working on it in VBA (as matter as fact, "Select" statements will slow you code down!).
So all instances that you have like this:
VBA Code: 
Range("D25").Select
Selection.ClearContents
can be simplified to this:
VBA Code: 
Range("D25").ClearContents
and that is true most anywhere you have one line of code end in "Select" and the next line of code begin with "Selection" or "ActiveCell".
 
Upvote 0
have not really understand.So in this case what do I change my code to? since I pasted it in my question. still stuck
 
Upvote 0
If you turn on the Macro Recorder, and record yourself hiding/unhiding sheets, you will get the VBA code that you need to hide/unhide the sheets.
 
Upvote 0
Sub TrainingDatabaseFinal2()
'
' TrainingDatabaseFinal2 Macro
' This macro submits and deletes data
'

'
Sheets("Intermediate").Select
Range("A2:Q2").Select
Selection.Copy
Sheets("DataBase").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("R3").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("Table1[[#Headers],[Training Type]]").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=-6
ActiveWindow.SmallScroll Down:=-3
ActiveCell.Offset(1, 0).Range("Table1[#Headers]").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data Entry").Select
ActiveWindow.SmallScroll Down:=21
Range("G33").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D33").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-6
Range("M25").Select
Selection.ClearContents
Range("J25").Select
Selection.ClearContents
Range("G25").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("M23").Select
Selection.ClearContents
Range("J23").Select
Selection.ClearContents
Range("G23").Select
Selection.ClearContents
Range("D23").Select
Selection.ClearContents
Range("D21").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-12
Range("G12").Select
Selection.ClearContents
Range("D12").Select
Selection.ClearContents
Range("M10").Select
Selection.ClearContents
Range("J10").Select
Selection.ClearContents
Range("G10").Select
Selection.ClearContents
Range("D10").Select
Selection.ClearContents
End Sub


That is my code..so were do I make ammendments?
 
Upvote 0
Please re-read my first post and at least attempt to make some of the changes that I recommend.
The best way to learn is to actual try some of this stuff, instead of us doing it all for you.
I give some pretty direct directions and show you examples of what you can do to clean up your code.

Also, in order to help with the hiding/unhiding part, it looks like your code refers to the "Intermediate" and "Database" sheets.
Which of the these sheets is hidden?
Is there any password protection on to unhide these sheets?
 
Upvote 0
Joe4 said:
Please re-read my first post and at least attempt to make some of the changes that I recommend.
The best way to learn is to actual try some of this stuff, instead of us doing it all for you.
I give some pretty direct directions and show you examples of what you can do to clean up your code.

Also, in order to help with the hiding/unhiding part, it looks like your code refers to the "Intermediate" and "Database" sheets.
Which of the these sheets is hidden?
Is there any password protection on to unhide these sheets?
Click to expand...
Thanks for the feedback. I can actually admit that I have been trying several attempts as you recommend but not able to manage. The sheet that is hidden is the 'intermediate' and 'database' sheets. when I unhide them the macro is able to run, but when I hide them, thats when I am getting the error
 
Upvote 0
fkapyela said:
Thanks for the feedback. I can actually admit that I have been trying several attempts as you recommend but not able to manage. The sheet that is hidden is the 'intermediate' and 'database' sheets. when I unhide them the macro is able to run, but when I hide them, thats when I am getting the error
Click to expand...

Have you turned on the Macro Recorder and manually unhidden those two sheets?
If you do (and then stop the Macro Recorder and view the code you recorded), this will give you the code you need to unhide the sheets.
Then copy/paste that code to the very top of your procedure to unhide the sheets before you work on them.

Then, you can do the same thing (using the Macro Recorder) to get the VBA code to hide the sheets again, and place that at the very bottom of your current procedure, after all the work has been finished.

If you still cannot get it to work, please post your updated code (with your attempt to incorporate this), and I will help you fix it up.
 
Upvote 0
Solution
If you clean-up all those unnecessary lines like I mentioned and add those sections to unhide/hide your sheets, your code should end up looking something like this:
VBA Code: 
Sub TrainingDatabaseFinal2()
'
' TrainingDatabaseFinal2 Macro
' This macro submits and deletes data
'

'Unhide sheets
Sheets("Intermediate").Visible = True
Sheets("Database").Visible = True
Sheets("Data Entry").Visible = True

Sheets("Intermediate").Select
Range("A2:Q2").Copy

'Potential for more clean-up in this block
Sheets("DataBase").Select
Range("R3").Select
Range("Table1[[#Headers],[Training Type]]").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("Table1[#Headers]").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Data Entry").Select

Range("G33").ClearContents
Range("D33").ClearContents
Range("M25").ClearContents
Range("J25").ClearContents
Range("G25").ClearContents
Range("D25").ClearContents
Range("M23").ClearContents
Range("J23").ClearContents
Range("G23").ClearContents
Range("D23").ClearContents
Range("D21").ClearContents
Range("G12").ClearContents
Range("D12").ClearContents
Range("M10").ClearContents
Range("J10").ClearContents
Range("G10").ClearContents
Range("D10").ClearContents

'Hide sheets
Sheets("Intermediate").Visible = False
Sheets("Database").Visible = False
Sheets("Data Entry").Visible = False

End Sub
Note that there is one section that could still be cleaned-up a little bit, but without seeing the structure of your table and data, I am hesitant to do that, because if I make an incorect assumption, it might not work properly.
 
Upvote 0
Thanks. This is the code which I have now after implementing your changes:

Sub TrainingDatabaseFinal2()
'
' TrainingDatabaseFinal2 Macro
' This macro submits and deletes data
'

' Unhidesheets Macro
' Unhiding sheets
Sheets("Dashboard").Select
Sheets("Intermediate").Visible = True
Sheets("Data Entry").Select
Sheets("DataBase").Visible = True
Sheets("Intermediate").Select
Range("A2:Q2").Select
Selection.Copy
Sheets("DataBase").Select
Range("R3").Select
Range("Table1[[#Headers],[Training Type]]").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=-6
ActiveWindow.SmallScroll Down:=-3
ActiveCell.Offset(1, 0).Range("Table1[#Headers]").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data Entry").Select
ActiveWindow.SmallScroll Down:=21
Range("G33").Select
Application.CutCopyMode = False
Range("D33").ClearContents
ActiveWindow.SmallScroll Down:=-6
Range("M25").ClearContents
Range("J25").ClearContents
Range("G25").ClearContents
Range("D25").ClearContents
Range("M23").ClearContents
Range("J23").ClearContents
Range("G23").ClearContents
Range("D23").ClearContents
Range("D21").ClearContents
Range("G12").ClearContents
Range("D12").ClearContents
Range("M10").ClearContents
Range("J10").ClearContents
Range("G10").ClearContents
Range("D10").ClearContents

HideSheets Macro
' This Macro hides sheets
'

'
Sheets("Intermediate").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("DataBase").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

I got this error

Compile error:
Wrong number of arguments or invalid property assignment
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Issue with Macros and renaming files
Replies
6
Views
478
kevin9999
K
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
125
sspatriots
S
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
670
cstuder
C
R
  • Question
Formatting using VBA
Replies
0
Views
410
RandyD123
R
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
72
willow1985
willow1985

Forum statistics

Threads
1,214,642
Messages
6,120,698
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top