MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Running Macros that refer to hidden worksheets


Posted by Meryn on August 06, 2001 6:00 AM

Is there anyway around this? At present if run a macro from a button set up on one worksheet - that refers to another worksheet that is hidden - I get an error. I know that I can protect the workshhets & hide the worksheet tabs so that they cannot be seen - but would prefer to hide them.


Posted by Cory on August 06, 2001 7:08 AM

You could do this:

Sub YourSub()
Application.ScreenUpdating = False
Sheet1.visible = xlsheetvisible
sheet1.activate
'your macro code here
sheet1.visible = vlsheethidden
application.screenupdating = true
End Sub

With the application.screenupdating deactivated and activated the way it is above, the user shouldn't even see what's being made visible and hidden until the macro is finished...

Cory

Posted by Meryn on August 06, 2001 7:39 AM

Perfect - and it tidies up my macro - thank you very much!
I have another question related to the same worksheet - which will probably be easy for you to answer...
I have the following:
1 workbook comprised of 3 worksheets.
The first worksheet(a) creates a label on wksheet(b) based on user input from(a) - the label(b) can be printed via a simple macro assigned to a button on (a)... easy so far...
the last sheet(c) needs to be updated each time the user inputs data on (a)- to keep a history of all the labels created. for this purpose i have created another button on (a).
my question is - how do i code so that all the information gets input into the appropriate row and then moves down a row so that the row above does not get overwritten the next time and so on...
any help would be most appreciated. thanks.

Posted by Cory on August 06, 2001 8:00 AM

What I gather from your description, you want to set up your sheet to where when a user enters their data, that data gets put on the bottom of a column of data in a place of it's own... Or, in other words, say you have a column A of user data. Another user comes along and enters his data. You want that data automatically put in the next available line under the present data... If that's not right, inquire again...

To do that, it depends where the user enters his data, but here's the code you can use to find the next available blank cell (if your data's in Col A...):

Sub NewData()
range("a1").select
selection.end(xlDown).offset(1,0).select
End Sub

If you can't figure out how to incorporate that into your code, just ask again with some more details...

Hope that helped...

Cory

Posted by Meryn on August 07, 2001 3:33 AM

That helped thanks v.much. I just changed the cell/col ref and my cursor ends up where I want it.
Now I want the data from the first sheet - to be input in the same line as the current cursor position.
for eg: i run the code you gave me and my cursor ends up at b28...
Sheets("a").Select
Range("B5").Select
Selection.Copy
Sheets("c").Select
ActiveSheet.Paste
...and then more copy & paste statements...

I want the first paste for the above to end up in b28, (where the cursor is) & the next copy & paste from a different cell ref on (a)to end up in (c) in ref c28, then d28 and so on across the sheet...
next time i input data into (a) sheet i run the code and my cursor will end up at b29..pastes to go into b29, c29, d29 etc..
apologies for spelling this out - but am just a novice at this (obviously) so need to get it straight in my own mind so that i can give you the correct info.
so i know i need something along the lines of current cursor position then +1 - or similar - but don't know the syntax.....
if i can get this sorted then i have the rest of my speadsheets completed with other simple coding that i have done... any help would be greatly appreciated. thks.

Posted by Cory on August 09, 2001 7:27 PM

I see what you're getting at. Not too difficult.
I use similar functions in some of my sheets.
The easiest way to do it is:

Say you want to begin pasting your values in 'c', cell A1, B1, ...
Put your code together something like this:

application.screenupdating = false
(all you copying code from 'a' goes here)
sheets("c").activate
range("a1").activate
if activecell.value = empty then
activesheet.paste
application.cutcopymode = false
range("a1").select
sheet("a").select
range("a1").select
application.screenupdating = true
elseif
activecell.offset(1,0).value = empty then
activecell.offset(1,0).activate
activesheet.paste
application.cutcopymode = false
range("a1").select
sheet("a").select
range("a1").select
application.screenupdating = true
else
activecell.end(xldown).offset(1,0).activate
activesheet.paste
application.cutcopymode = false
range("a1").select
sheet("a").select
range("a1").select
application.screenupdating = true
end if

The 'if' block starts after everything is copied, sheet 'c' is activated, and you're ready to find the place to paste it.

The first 'if' tests cell A1 to see if anything's in there (there wouldn't be if it's a fresh file...). If there's not, then paste here...

The first 'elseif' tests the cell underneath A1 (A2) to see if it's empty (and it would be if there's only been one entry made so far...). If it is, activate that cell (A2) and paste it there...

The last 'else' block only runs if the first two don't work. It says, "OK, the first two rows are empty. That means there's a list started already, and I need to move to the bottom of it, select the cell underneath the last row of data, then paste the new stuff there..."

I don't normally explain how the code works, but just a couple of months ago, I was asking easier questions than this one:-) So recently, in fact, I don't think my first few postings have been archived yet. If you scroll to the bottom and start working up, they're somewhere around there:-)


If you need more, just ask...


Cory