title: Select worksheet in Excel add-in to run VBA macro
body: <p>I have created an add-in using VBA which is a workbook with calculations. The add-in has a userform to extract relevant information from access database and populates the workbook. After the data is populated, calculations are performed in Sheet1. I need to paste the worksheet "Sheet1" from the add-in worksheet to a new workbook on running the add-in macro. </p>
  
  <p>When I run the add-in however, the worksheet appears to be hidden so my data is not updating. I get this error:
  " Run-time error '1004': Method 'Worksheets' of object '_Global' failed". </p>
  
  <p>Can someone tell me how to work with an add-in which has a worksheet where the required calculations are performed?</p>
  
  <p>The intriguing part is when I load the add-in after removing it from the list of add-ins in excel, it runs perfectly. But when I re-run the macro, the worksheet becomes hidden, so the same error appears. I am fairly new to VBA so any suggestions would be appreciated! </p>
  
  <p><em><strong>Edit</em></strong></p>
  
  <p>Code:</p>
  
  <pre><code>Private Sub OptionOK_Click() 'On selecting OK from userform
    Dim ws1 As Worksheet
    Sheets("Sheet1").Visible = True 
    Set ws1 = Worksheets("Sheet1") 
  
   'User Form Validation 
    If Trim(Me.cboData.value) = "" Then 
      Me.cboData.SetFocus 
      MsgBox "Please complete the form" 
      Exit Sub 
    End If 
  
   'copies data to given cell in excel     
    ws1.Range("A1").value = Me.cboData.value 
  
   'To copy selection from "Sheet1" into new workbook 
  Workbooks("myaddin.xlam").Sheets(1).Copy 
  End Sub 
  </code></pre>
  
  <p>I get the error on ...> Sheets("Sheet1").Visible = True.</p>