Object required error for named worksheet

Question

 Set Nightletter = ActiveWorkbook

 Nightletter.Sheets("XPT SUMMARY").Activate

 Range("A2").Activate

for this code also it shows Object Required error on 2nd line. I changed it to

Nightletter.Sheets("XPT SUMMARY").Activate

Activesheet.Range("A2").Activate

It also didn't work.

Answer 1

In case the suggestions in the comments above haven't yet solved your issue, please try to do the following.

Open a fresh copy of Excel. Create a new workbook, and save it as "myWorkbook.xlsm" - a workbook with macros enabled. Make sure it has at least two sheets.

Now open the VBA editor, create a new module, and enter the following code:

Option Explicit

Sub actSheet()
  ' simple code to activate Sheet2 in myWorkbook.xlsm
  Dim wb as Workbook
  Dim ws as Worksheet

  Set wb = Application.Workbooks("myWorkbook.xlsm")
  Set ws = wb.Sheets("Sheet2")

  If Not ws.Visible = xlSheetVisible Then ws.Visible = xlSheetVisible
  ws.Activate

End Sub

This is using what I believe are Best Practices for what you are trying to do (although it's almost never necessary to Activate a worksheet - see the excellent link in Siddharth Rout's comment above). Specifically:

• Start with Option Explicit (so you must declare each variable)
• Include a comment to describe your function
• Declare all variables used
• Build up reference to sheet from workbook, and by name: name less likely to change than order
• Make sure that the sheet you want to activate is Visible

Finally - compare to what you are doing with the above; if you really can't tell the difference, and you are still getting the error, come back and tell us.