-5

There seems to be something wrong with one of my Excel Objects. Take a look at the below snippet. I created the subroutine Run_all(), and when I step through, it'll go up to the Function row_count() and start executing, however, when it gets to the first highlighted row, I get the error.

**The Sheet that I'm referencing in the function is typed correctly. For example, if I run the bottom subroutine CA_Copy_Paste_, it works correctly and I get no errors.

Why is Excel not recognizing "Sheet 3" in the function? For more context, it only works if I type "Sheet4". Does not work on "Sheet1" or "Sheet2" either.

This image shows the portion of my VBA in question. Since this is my first question, I cannot embed images, but have to use the below link

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
T. Price
  • 1
  • 1
  • 2
  • 1
    Because you need `Sheets("Sheet 3").Activate` before that line. You can't target a cell with activate when you're not on the sheet - even when you explicitly refer to the sheet name. It's working with `Sheet4` probably because that's your `Activesheet` – dwirony Nov 21 '17 at 21:23
  • 4
    1. please put the code itself in the question as text not a photo, we cannot copy paste a photo. 2. There are better methods of finding the first empty cell in a column, that does not use a loop, which is the slowest method. See here:https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba 3. avoid `.Activate` and `.Select`, again they slow down the code. See Here: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Nov 21 '17 at 21:24
  • `Rowcount = Worksheets("Sheet 3").Cells(Worksheets("Sheet 3").Rows.Count,3).End(xlUp).Row+1` will return the row after the last row with data in column C. – Scott Craner Nov 21 '17 at 21:27
  • Just an aside to this, that entire function (although it doesn't need to be a function as you aren't passing anything to or from it, it could just be a sub) can be done in one line: `rowcount = Sheets("Sheet 3").Range("C" & rows.count).end(xlup).row` This avoids looping and selecting which is always a good thing Also, make sure you define rowcount as a long, not an integer – Dan Donoghue Nov 21 '17 at 22:14

1 Answers1

1

If you insist on using .Select and .Activate to accomplish your goals then you must activate the worksheet before activating or selecting a cell or range of cells on that worksheet.

worksheets("sheet 3").activate
activesheet.range("c4").activate

If you wish to use some 'shorthand' to try and accomplish this in a single line of code then switch to the Application.GoTo method.

Application.Goto reference:=worksheets("sheet 3").range("c4")

In any event, you are best off avoiding the use of select and activate. See How to avoid using Select in Excel VBA.

  • fwiw, I find it odd that you would intentionally rename your worksheet just to put a space between *sheet* and *3*. –  Nov 21 '17 at 22:06