How Can I make a Macro work to Hidden Sheets?

[mrgillus]

OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"

Is there anyway around this?

Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?

Thanks for the advice!

[Andy Pope]

You can not use code that has .Select or Selection in it if the sheet is hidden.

You can still manipulate the hidden sheet but you must adjust your code.

[mrgillus]

http://www.excelforum.com/excel-prog...en-sheets.html

Previous Post--

So can you do something other than selecting them to make this work? Maybe activate?

[Andy Pope]

It really depends on what 'make this work" is.

for example.

Please Login or Register  to view this content.

[mrgillus]

Ok.. Let me work with that for I while.. I have a lot of lines of code to go through and correct due to it being created mostly in a macro but I will get back to ya!

Thanks for the help!!

[mrgillus]

Ok this appears to work! Just tested on my first copy method..

The code that didn't work with hidden sheets was:

Please Login or Register  to view this content.

The method that works with the hidden sheet is:

Please Login or Register  to view this content.

Let me know if that is the proper way to write it.. It seems to be working that way though.

[mrgillus]

Please Login or Register  to view this content.

I'm struggling with this bit of code.. Basically I need to rewrite it so that it will work since both the sheets involved here are hidden..

Thanks!

[Andy Pope]

Please Login or Register  to view this content.

[mrgillus]

Hmm.. Well that is working to the point that it does not start copying at the end of the last cell that contains data. Is the End(xlUp) causing it to jump back up before it pastes?

[mrgillus]

Okay it works, i fix..

Turns out my merged cell at the bottom that I was using as a stopper of sorts stopped working during the conversion of the code..

All I did was add a letter in the merged cell at the bottom and changed the font color to white so it was not visible and now the macro knows where to stop.

Thanks a bunch for the help.. I'll see if I can't stumble through the remainder of the code and get back to you if I have any more problems.

[mrgillus]

I have another section of code that is giving me trouble.. Basically I'm trying to use the existing line with an input box.. Here is what I got:

First is the Inputbox section.. FYI I'm a noob so I made this work however I could get it.. with the help of Roy that is! Thanks Roy!

Then the inputbox references to my macro and basically the macro takes over the paste operation of this task.

Please Login or Register  to view this content.

This part works just fine Basically it copies the section I need and then the macro would paste it, remember that this code needs to work with hidden sheets..

Please Login or Register  to view this content.

The Line highlighted in red is where my error occurs.
"Run-time error '438':"
"Object doesn't support this property or method."

Maybe I could get some help with this line of the code? Just need this to paste on the next empty line down just like one of Andy's previous posts where the line looked like:

Please Login or Register  to view this content.

Thanks again all for the help!

[shg]

The red line references a cell but doesn't do anything.

[Andy Pope]

Your red line fails as it not really doing anything where it is.

In the example I posted the style of code was used to determine the destination cell.

so maybe,

Please Login or Register  to view this content.

[mrgillus]

Yeh.. Okay cool that works, But I had to write it into the commandbutton sub instead of sending it to a macro.. :

Please Login or Register  to view this content.

That is the completed code working!