VB in Excel: Run User Form works, Button_click doesn't?

Sheet1 has a button, and when I click on it then it should run the Private Sub Create_Route_Click()

When I run the form from within VB, it works perfectly.

But when I click the button, then it starts to run, but gives me an error when it tries to copy Sheet1. Why?

Extract from VB code:

Private Sub Create_Route_Click()

Dim XL As Excel.Application

Set XL = Excel.Application

Dim WB As Excel.Workbook

'Dim <all other variables>...

'

Sheets("Sheet1").Select

Sheets("Sheet1").Copy Before:=Sheets(2) ' error occurs here

' tons of more code follows, which works perfectly....

End Sub

Update:

Mark G.: This does not solve the problem.

The issue is: Why does it work perfectly when I run the code from the editor, but creates that error when the user clicks the button, which calls exactly the same Sub?

1 Answer

Relevance
  • MarkG
    Lv 7
    1 decade ago
    Favorite Answer

    <<<Sheets("Sheet1").Copy Before:=Sheets(2) ' error occurs here>>>

    I think you have to use the name of the sheet in the Before Statement which is expecting a string and you're pass a sheet object.

    Before:="Sheet2" or you have to use the name property of Sheets(2)

Still have questions? Get your answers by asking now.