0

I have just started learning Macros. When I click on the Macros button to create a report, I get the error

method 'cells' of object '_global' failed.

The code is:

Workbooks(watP).Activate
Cells(1, 1) = "List"

And I am getting the error in this line: Cells(1, 1) = "List"

Can anyone please help me here?

Vityata
  • 42,633
  • 8
  • 55
  • 100
Aayush Vohra
  • 1
  • 3
    This [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) will get you started. If you follow what has been advised there you will not face these problems :) – Siddharth Rout Sep 21 '20 at 11:52
  • May be its due to worksheet...try this Workbook(watP).worksheets("Your sheet name).cells(1,1)="List" – MD Ismail Hosen Sep 21 '20 at 12:10
  • Agree with @SiddharthRout that you should avoid `select` but in this case I don't think that's causing the problem.You either need a sheet reference (either `activesheet.cells(1,1) = List` or as above `sheets(sheetname).cells(1,1) = List`, or try `Range("A1") = List` – Spencer Barnes Sep 21 '20 at 13:13
  • @SpencerBarnes: Feel free to disagree but I guess you missed my answer there? – Siddharth Rout Sep 21 '20 at 13:27
  • @SiddharthRout correct, sorry!! – Spencer Barnes Sep 22 '20 at 06:34

1 Answers1

1

Workbooks(watP).Activate activates the workbook with a name, in the variable watP. The active worksheet is not mentioned. To see the ActiveSheet, run this:

Workbooks(watP).Activate
MsgBox ActiveWorkbook.ActiveSheet.Name

Then, most probably somehow the cell A1 of the worksheet, displayed in MsgBox() is not to be written into, because it is not the correct one you are expecting. Try this:

Sub TestMe()
    
    Dim wbkName As String
    Dim wksName As String
    
    wbkName = "Book1"   'change it to the real name of the workbook
    wksName = "Sheet5"  'change it to the real name of the worksheet
    
    Workbooks(wbkName).Worksheets(wksName).Cells(1, 1) = "List"

End Sub

After some time, when you start feeling nice and accustomed to VBA, you may take a look at one of the best practices, discussed here - How to avoid using Select in Excel VBA.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Hi,Thanks for the response. This is the entire code: – Aayush Vohra Sep 22 '20 at 09:45
  • Hi, Thanks for the reply. I entered MsgBox ActiveWorkbook.ActiveSheet.Name, but got a blank popup. I also added the TestMe() function, but I am not sure of the worksheet. Can you please help more here? – Aayush Vohra Sep 22 '20 at 09:48
  • Here is complete code: Workbooks(watP).Activate MsgBox ActiveWorkbook.ActiveSheet.Name Workbooks(watP).Worksheets("Index").Cells(1, 1) = "List" Sheets("Index").Select Columns(1).AdvancedFilter action:=xlFilterCopy, copytorange:=Range("b1"), unique:=True Columns("A:A").Select Selection.Delete Shift:=xlToLeft Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Rows("1:1").Select Selection.Delete Shift:=xlUp – Aayush Vohra Sep 22 '20 at 09:54
  • @AayushVohra - try to do it on an new Excel workbook. Concerning the worksheet - you may use `ThisWorkbook.Worksheets("Name_of_worksheet")` instead of the `ActiveSheet`. Instead of "Name_of_worksheet", write the real name, usually "Sheet1". – Vityata Oct 14 '20 at 17:49