1

While this seems very basic, I am continually getting an error message while trying to select a cell in a certain sheet on my workbook in my Macro. Does any one know why this will not work? I'm getting error message Run Time Error '1004'. The sheets name is "Sheet1"and my code is below:

 Application.ActiveWorkbook.Worksheets("Sheet1").Range("N2").Select
BerticusMaximus
  • 705
  • 5
  • 16
  • Select the sheet first `Sheets("Sheet1").Select` – user1274820 Oct 10 '17 at 19:10
  • `Application.Goto (ActiveWorkbook.Sheets("Sheet1").Range("N2"))` https://support.microsoft.com/en-us/help/291308/how-to-select-cells-ranges-by-using-visual-basic-procedures-in-excel – luke Oct 10 '17 at 19:29
  • What else is your code doing? Could you post the whole thing? There might be something happening before this that's giving you issues. Then it would be good to review [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BerticusMaximus Oct 10 '17 at 19:41
  • Thanks everyone, the first suggestion worked. I'll have to try the Goto command as well for future uses. I changed the code to Sheets("Sheet1").Select Range("N2").Select –  Oct 10 '17 at 19:53
  • @luke `Application.Goto (ActiveWorkbook.Sheets("Sheet1").Range("N2"))` is equivalent to `Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("N2").Value`. I think you meant to say `Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("N2")`. – YowE3K Oct 10 '17 at 19:59

1 Answers1

0

It's bad practice to use ActiveWorkbook when you don't need to. It's always better to set your workbooks and worksheets to actual variables that you can call on. I think your code is activating another workbook then trying to select a range in a worksheet it can't find.

Sub TryThis()

    Dim wbk As Workbook
    Dim ws As Worksheet

    Set wbk = Workbooks("myWorkbook.xlsm")
    Set ws = wbk.Worksheets("Sheet1")

    'Now when we say "ws." it is actually "Workbooks("myWorkbook.xlsm").Worksheets("Sheet1")."

    'This is okay to start with but it's better to work with the cells directly
    ws.Select
    Range("N2").Select
    Selection = "myText"

    'This is much faster and you won't have to worry about what is currently selected
    ws.Range("N2") = "myText"

End Sub
BerticusMaximus
  • 705
  • 5
  • 16