Select worksheet in Excel add-in to run VBA macro

Question

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.

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".

Can someone tell me how to work with an add-in which has a worksheet where the required calculations are performed?

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!

Edit

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

I get the error on ...> Sheets("Sheet1").Visible = True.

VBAlearner VBAlearner · Accepted Answer · 2013-10-02T13:25:57

I just realized that I had to use "ThisWorkbook" in the add-in VBA code.

Set ws1 = ThisWorkbook.Sheets ("Sheet1")

VBA code within a workbook should use "ThisWorkbook" to reference to sheets or ranges inside the add-in.

Select worksheet in Excel add-in to run VBA macro

2 Answers