Work on a hidden worksheet
 

I have a program that copies a hidden worksheet, and then makes the
copied sheet visible. However, I can't seem to select a range in the
new sheet, or do much of anything to it.

I had other parts of the program working on the copied sheet when it
was being copied from a non-hidden worksheet. Now that the sheet comes
from a hidden sheet, these parts don't work, even though I made the
sheet visible.

Anyone know why this might be?

Thanks for any help.


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

Selects are rarely necessary. You will use them a lot when you first get
started because that is what the macro recorder does, but in time you will be
able to get away from them. By avoiding the selects you can even work on
hidden sheets. If you will post some code we can probably let you know why
your code is not working.
--
HTH...

Jim Thomlinson


"agentsmith83" wrote:


I have a program that copies a hidden worksheet, and then makes the
copied sheet visible. However, I can't seem to select a range in the
new sheet, or do much of anything to it.

I had other parts of the program working on the copied sheet when it
was being copied from a non-hidden worksheet. Now that the sheet comes
from a hidden sheet, these parts don't work, even though I made the
sheet visible.

Anyone know why this might be?

Thanks for any help.


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

"raw" is a sheet where I'm entering data, including a date in cell B8.
"blank" is a hidden sheet, which gets copied. The copied sheet is made
visible, and renamed to the first 3 letters of the month and the last 2
digits of the year.

Sheets("blank").Copy Befo=Sheets("raw")
Worksheets("blank (2)").Visible = True
Worksheets("blank (2)").Select

MoYr = Mid(MonthName(month(Range("B8"))), 1, 3) & " " &
Mid(Year(Worksheets("raw").Range("B8")), 3, 2)
ActiveSheet.Name = MoYr

Now, what happens here is that the range referred to in the "MoYr ="
line is the date in cell B8 of the "raw" worksheet, even though "blank
(2)" is selected. Also, "blank (2)" is renamed to MoYr when the
ActiveSheet command is used. So, this actually works, in that it does
what I want it to do, but I seem to run into problems later on....

Worksheets("raw").Select
Range("C8", Range("C8").End(xlDown)).Copy
Worksheets(MoYr).Select
With ActiveSheet
..Range("B35").PasteSpecial
..Range("C35", Range("I35").End(xlDown)).ClearContents
..Selection.Merge (True)
End With

The error occurs on the red line of code. I'm able to paste in to the
MoYr sheet, but the clear contents command gives me an error. I also
tried selecting that range, and then clearing it, but it would error on
the select command line.

I believe it has to do with the worksheet having been copied from a
hidden sheet, because the same code worked when I was copying from a
non-hidden worksheet.

Thanks for the help,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

Try this...

Sheets("blank").Copy Befo=Sheets("raw")

with Worksheets("blank (2)")
..Visible = True
MoYr = Mid(MonthName(month(.Range("B8"))), 1, 3) & " " & _
Mid(Year(Worksheets("raw").Range("B8")), 3, 2)
..Name = MoYr
end with

with Worksheets("raw")
.Range(.range("C8"), .Range("C8").End(xlDown)).Copy
end with

With Worksheets(MoYr)
..Range("B35").PasteSpecial
..Range("C35", Range("I35").End(xlDown)).ClearContents
..Selection.Merge (True)
End With

--
HTH...

Jim Thomlinson


"agentsmith83" wrote:


"raw" is a sheet where I'm entering data, including a date in cell B8.
"blank" is a hidden sheet, which gets copied. The copied sheet is made
visible, and renamed to the first 3 letters of the month and the last 2
digits of the year.

Sheets("blank").Copy Befo=Sheets("raw")
Worksheets("blank (2)").Visible = True
Worksheets("blank (2)").Select

MoYr = Mid(MonthName(month(Range("B8"))), 1, 3) & " " &
Mid(Year(Worksheets("raw").Range("B8")), 3, 2)
ActiveSheet.Name = MoYr

Now, what happens here is that the range referred to in the "MoYr ="
line is the date in cell B8 of the "raw" worksheet, even though "blank
(2)" is selected. Also, "blank (2)" is renamed to MoYr when the
ActiveSheet command is used. So, this actually works, in that it does
what I want it to do, but I seem to run into problems later on....

Worksheets("raw").Select
Range("C8", Range("C8").End(xlDown)).Copy
Worksheets(MoYr).Select
With ActiveSheet
.Range("B35").PasteSpecial
.Range("C35", Range("I35").End(xlDown)).ClearContents
.Selection.Merge (True)
End With

The error occurs on the red line of code. I'm able to paste in to the
MoYr sheet, but the clear contents command gives me an error. I also
tried selecting that range, and then clearing it, but it would error on
the select command line.

I believe it has to do with the worksheet having been copied from a
hidden sheet, because the same code worked when I was copying from a
non-hidden worksheet.

Thanks for the help,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

I've now tried changing this program so that the worksheet is not
hidden, and I still have the same problem. So the worksheet being
hidden doesn't seem to have anything to do with it.


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

Still no luck. Thanks for your reply though.

One thing I didn't mention, which I just realized, is that this is code
I'm trying to add to a button in the "raw" worksheet. Do you know if
this is why I'm having trouble working in another worksheet?

Thanks again,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

This only becomes an issue if you used a button from the control toolbox. If
you did then the code for the button is in the sheet and not in a general
module. Code that is in a sheet refers to that sheet unless otherwise
speicfied. Code in a module refers to the activesheet unless otherwise
specified. But the code that I posted explistly references the shee to act
upon so that should not be an issue unless there is more code than you are
posting. There is however one line of code that will cause a problem and that
is

Selection.Merged (True)

What range is this supposed to merge...
Try this...
With Worksheets(MoYr)
..Range("B35").PasteSpecial
..Range("C35", Range("I35").End(xlDown)).ClearContents
..Selection.Merge (True)
End With
--
HTH...

Jim Thomlinson


"agentsmith83" wrote:


Still no luck. Thanks for your reply though.

One thing I didn't mention, which I just realized, is that this is code
I'm trying to add to a button in the "raw" worksheet. Do you know if
this is why I'm having trouble working in another worksheet?

Thanks again,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837

Work on a hidden worksheet
 

So, after playing with it some more, it turns out to be a problem with
the end.xlDown command I was using, caused by the way I had some cells
merged. I haven't got it quite yet, but I'll be working on it more
soon. Jim, thanks for all your input on this though. I'll post again
when I get it.

Thanks,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php...o&userid=33600
View this thread: http://www.excelforum.com/showthread...hreadid=533837