select method of range class fails

  • Thread starter Thread starter mark kubicki
  • Start date Start date
M

mark kubicki

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
 
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

Sub cbGenerateIssue_Click()
Sheets("Issue worksheet").Copy Before:=Sheets(1).Name = "1st Issue"

with sheets("1st Issue")
.Shapes("cbReformat").Delete
.Shapes("cbGenerateIssue").Delete
'you shouldn't need to select but you don't say what is next
.select
.Rows("5:7").Select
end with
 
sorry... but it doesn't for me...



Glenn Ray said:
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
Click to expand...
 
sorry to say, but that didn't work either...



Don Guillett said:
Mark

Sub cbGenerateIssue_Click()
Sheets("Issue worksheet").Copy Before:=Sheets(1).Name = "1st Issue"

with sheets("1st Issue")
.Shapes("cbReformat").Delete
.Shapes("cbGenerateIssue").Delete
'you shouldn't need to select but you don't say what is next
.select
.Rows("5:7").Select
end with
Click to expand...
 
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
 
try it this way

Sub cbGenerateIssue_Click()
Sheets("Issue worksheet").Copy Before:=Sheets(1)
with activesheet
.Name = "1st Issue"
.Shapes("cbReformat").Delete
.Shapes("cbGenerateIssue").Delete
'you shouldn't need to select but you don't say what is next
.select
.Rows("5:7").Select
end with



Don Guillett
SalesAid Software
(e-mail address removed)
 
Try re-selecting the sheet...

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)
Sheets("1st Issue").Select
Rows("5:7").Select

Selecting can be a little problematic. I avoid selecting wherever possible
so I hope this works...

HTH
 
Jim Thomlinson said:
Try re-selecting the sheet...

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)
Sheets("1st Issue").Select
Rows("5:7").Select

Selecting can be a little problematic. I avoid selecting wherever possible
so I hope this works...

HTH
Click to expand...
 
what i'm needing to do is convert the formula result across the sheet to
values... maybe instead of trying to fix this mess, there's a different
(more efficient) way??
 
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)
Activesheet.Rows("5:7").Select
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select method of Range class failed 4
Excel VBA help: Text file formatting 19
skip actions within a macro if a cell value is zero 1
Run-time error 1004 paste method of worksheet class failed 3
" Select method of range class failed" if sheet not on top 4
select method of range class failing ? 6
Deleting a range after selecting a sheet 1
Selection after copy sheet failed 8

Back
Top