Re: Work on a hidden worksheet

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Try this...

Sheets("blank").Copy Before:=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 *** where I'm entering data, including a date in cell B8.
"blank" is a hidden ***, which gets copied. The copied *** is made
visible, and renamed to the first 3 letters of the month and the last 2
digits of the year.

Sheets("blank").Copy Before:=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)
Active***.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" work***, even though "blank
(2)" is selected. Also, "blank (2)" is renamed to MoYr when the
Active*** 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 Active***
.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 ***, 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 work*** having been copied from a
hidden ***, because the same code worked when I was copying from a
non-hidden work***.

Thanks for the help,
Chris


--
agentsmith83
------------------------------------------------------------------------
agentsmith83's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33600
View this thread: http://www.excelforum.com/showthread.php?threadid=533837


.

Quantcast