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.
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
>
>
"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 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)
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
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.
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 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 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)
> 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
>
>
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
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
>
>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks