macro error message

[purplehaz]

I'm writing some code(in excel, visual basic). The following code is
attached to the click action of a button.



Private Sub cmdNewPayApp_Click()

Sheets("Pay App").Visible = True

Sheets("Pay App").Select

Sheets("Pay App").Copy After:=Sheets(2)

Sheets("Pay App (2)").Select

Sheets("Pay App (2)").Name = "Pay App 1"

Sheets("Pay App").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("Pay App 1").Select

Range("O11").Select

ActiveCell.Formula = "=A18"

Range("O13").Select

ActiveCell.Formula = "=A19"

Range("O15").Select

ActiveCell.Formula = "=A20"

Range("O17").Select

ActiveCell.Formula = "=A21"

Range("A3").Select

End Sub





When it gets to the code: Range("O11").Select

I always get this error:



Run-time error '1004'

Select method of range class failed.



I thought the syntax is correct. Anyone know why the error occurs?

BTW - if I divide the code into two parts attaching the second part(error
part) to it's own button click action, run the first part, then click the
second button and run the second part it works fine. Why does it not work
together?

Thanks for any help.

 

[J.E. McGimpsey]

Not sure why you're getting the error - works fine for me.

However, you don't need to do all those selections. Working with the
objects directly:

Private Sub cmdNewPayApp_Click()
Sheets("Pay App").Copy After:=Sheets(2)
With ActiveSheet
.Name = "Pay App 1"
.Range("O11").Formula = "=A18"
.Range("O13").Formula = "=A19"
.Range("O15").Formula = "=A20"
.Range("O17").Formula = "=A21"
.Visible = True
.Range("A3").Select
End With
End Sub

 

[purplehaz]

Thanks for the info. I think the worksheet got corrupted somehow. I'm going
to create a fresh one and then put the code in. I'm kinda new at vb so
thanks for the tips on streamlining the code.

 

[purplehaz]

BTW - just deleted my old code, saved the spreadsheet, entered in my old
code again and it worked. I since switched to your code. I've read a bit
about the With syntax, but didn't know how to use it. Your example sheds
some light on it. Thanks again, I'm all fixed up now.

Thanks for the info. I think the worksheet got corrupted somehow. I'm going
to create a fresh one and then put the code in. I'm kinda new at vb so
thanks for the tips on streamlining the code.