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").Copy Before:=Sheets(2) ' error occurs here
' tons of more code follows, which works perfectly....
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?
- MarkGLv 71 decade agoFavorite 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)