Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
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
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
- ?Lv 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)