select method of range class fails

sorry... but it doesn't for me...



"Glenn Ray" <GlennRay@discussions.microsoft.com> wrote in message
news:9D3D2B70-2CC8-4159-A424-CFEFCE8C83E9@microsoft.com...
> This should work:
> Sub cbGenerateIssue_Click()
> Sheets("Issue worksheet").Copy Before:=Sheets(1)
> With Sheets(1)
> .Name = "1st Issue"
> .Shapes("cbReformat").Delete
> .Shapes("cbGenerateIssue").Delete
> End With
> Rows("5:7").Select
> End Sub
>
> It will only work once. If you try it again, it will halt when it tries
to
> rename the new sheet "1st Issue", which would already exist. You could
> insert code to remove any existing sheet with that name first.
>
> -Glenn Ray
>
>
> "mark kubicki" wrote:
>
> > select method of range class fails (AUGHHH!!!)
> > sheet is unprotected
> > sheet has a control button "cbGenerateIssue"
> > code had been created by recording a macro (where it worked...)
> > select fails even if control button , which calls the code, has not yet
been
> > deleted
> >
> >
> > Private Sub cbGenerateIssue_Click()
> > Sheets("Issue worksheet").Select
> > Sheets("Issue worksheet").Copy Before:=Sheets(1)
> > Sheets("Issue worksheet (2)").Select
> > Sheets("Issue worksheet (2)").Name = "1st Issue"
> > ActiveSheet.Shapes("cbReformat").Select
> > Selection.Delete
> > ActiveSheet.Shapes("cbGenerateIssue").Select
> > Selection.Delete
> > ' --------> this next line fails (or any variation of it)
> > Rows("5:7").Select
> >
> >
> >
> > had tried some variations:
> > ...Sheets("1st Issue).Rows("5...
> > ...put the select into a function
> > ... moved the calling control deletion to after select...
> > nothing
> >
> > thanks in advance,
> > mark
> >
> >
> >

Is the error occurring at the same line in the code (Rows("5:7").Select) ?
Is the error the same (select method of range class fails)?

What if you inserted a message box before the select:

MsgBox "New Sheet inserted."
Rows("5:7").Select

Just curious if you get an error at the message box command or the select
command. The subroutine worked fine for me.
-Glenn

"mark kubicki" wrote:

> sorry... but it doesn't for me...
>
>
>
> "Glenn Ray" <GlennRay@discussions.microsoft.com> wrote in message
> news:9D3D2B70-2CC8-4159-A424-CFEFCE8C83E9@microsoft.com...
> > This should work:
> > Sub cbGenerateIssue_Click()
> > Sheets("Issue worksheet").Copy Before:=Sheets(1)
> > With Sheets(1)
> > .Name = "1st Issue"
> > .Shapes("cbReformat").Delete
> > .Shapes("cbGenerateIssue").Delete
> > End With
> > Rows("5:7").Select
> > End Sub
> >
> > It will only work once. If you try it again, it will halt when it tries
> to
> > rename the new sheet "1st Issue", which would already exist. You could
> > insert code to remove any existing sheet with that name first.
> >
> > -Glenn Ray
> >
> >
> > "mark kubicki" wrote:
> >
> > > select method of range class fails (AUGHHH!!!)
> > > sheet is unprotected
> > > sheet has a control button "cbGenerateIssue"
> > > code had been created by recording a macro (where it worked...)
> > > select fails even if control button , which calls the code, has not yet
> been
> > > deleted
> > >
> > >
> > > Private Sub cbGenerateIssue_Click()
> > > Sheets("Issue worksheet").Select
> > > Sheets("Issue worksheet").Copy Before:=Sheets(1)
> > > Sheets("Issue worksheet (2)").Select
> > > Sheets("Issue worksheet (2)").Name = "1st Issue"
> > > ActiveSheet.Shapes("cbReformat").Select
> > > Selection.Delete
> > > ActiveSheet.Shapes("cbGenerateIssue").Select
> > > Selection.Delete
> > > ' --------> this next line fails (or any variation of it)
> > > Rows("5:7").Select
> > >
> > >
> > >
> > > had tried some variations:
> > > ...Sheets("1st Issue).Rows("5...
> > > ...put the select into a function
> > > ... moved the calling control deletion to after select...
> > > nothing
> > >
> > > thanks in advance,
> > > mark
> > >
> > >
> > >
>
>
>

the error is at the command (the box displays without a problem)



"Glenn Ray" <GlennRay@discussions.microsoft.com> wrote in message
news:3801AC3B-B7C4-483E-BD19-548415227266@microsoft.com...
> Is the error occurring at the same line in the code (Rows("5:7").Select) ?
> Is the error the same (select method of range class fails)?
>
> What if you inserted a message box before the select:
>
> MsgBox "New Sheet inserted."
> Rows("5:7").Select
>
> Just curious if you get an error at the message box command or the select
> command. The subroutine worked fine for me.
> -Glenn
>
> "mark kubicki" wrote:
>
> > sorry... but it doesn't for me...
> >
> >
> >
> > "Glenn Ray" <GlennRay@discussions.microsoft.com> wrote in message
> > news:9D3D2B70-2CC8-4159-A424-CFEFCE8C83E9@microsoft.com...
> > > This should work:
> > > Sub cbGenerateIssue_Click()
> > > Sheets("Issue worksheet").Copy Before:=Sheets(1)
> > > With Sheets(1)
> > > .Name = "1st Issue"
> > > .Shapes("cbReformat").Delete
> > > .Shapes("cbGenerateIssue").Delete
> > > End With
> > > Rows("5:7").Select
> > > End Sub
> > >
> > > It will only work once. If you try it again, it will halt when it
tries
> > to
> > > rename the new sheet "1st Issue", which would already exist. You
could
> > > insert code to remove any existing sheet with that name first.
> > >
> > > -Glenn Ray
> > >
> > >
> > > "mark kubicki" wrote:
> > >
> > > > select method of range class fails (AUGHHH!!!)
> > > > sheet is unprotected
> > > > sheet has a control button "cbGenerateIssue"
> > > > code had been created by recording a macro (where it worked...)
> > > > select fails even if control button , which calls the code, has not
yet
> > been
> > > > deleted
> > > >
> > > >
> > > > Private Sub cbGenerateIssue_Click()
> > > > Sheets("Issue worksheet").Select
> > > > Sheets("Issue worksheet").Copy Before:=Sheets(1)
> > > > Sheets("Issue worksheet (2)").Select
> > > > Sheets("Issue worksheet (2)").Name = "1st Issue"
> > > > ActiveSheet.Shapes("cbReformat").Select
> > > > Selection.Delete
> > > > ActiveSheet.Shapes("cbGenerateIssue").Select
> > > > Selection.Delete
> > > > ' --------> this next line fails (or any variation of it)
> > > > Rows("5:7").Select
> > > >
> > > >
> > > >
> > > > had tried some variations:
> > > > ...Sheets("1st Issue).Rows("5...
> > > > ...put the select into a function
> > > > ... moved the calling control deletion to after select...
> > > > nothing
> > > >
> > > > thanks in advance,
> > > > mark
> > > >
> > > >
> > > >
> >
> >
> >