Avatar of Geekamo
Geekamo
Flag for United States of America asked on

How do I select a specific cell?

I am using the following code:


Sub ConvertToTable()
Dim tbl As Range Dim ws As Worksheet     For Each ws In ActiveWorkbook.Sheets         If ws.Name <> "Summary" Then         Set tbl = ws.Range("A1").CurrentRegion       ws.ListObjects.Add(SourceType:=xlSrcRange, XLListObjectHasHeaders:=xlYes, Source:=tbl).Name = ws.Name     Range("A1").Select         End If     Next ws End Sub

Open in new window


I added: Range("A1").Select


When this workbook is auto-generated, all worksheets have cell A1 selected EXCEPT the last page (which is currently selected on cell J54).


By adding "Range("A1").Select", my goal was to ensure that after the tables were created, it would move the cursor into cell A1.


So that when the code has finished running, if I navigate through each worksheet, A1 is selected on every sheet.


But, it doesn't work... Any ideas why?


(On a side note, if I run that line of code on it's own - it does select the correct cell)

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Geekamo

8/22/2022 - Mon
Martin Liss

It works for me. I added line 10.

Sub ConvertToTable()

Dim tbl As Range
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Summary" Then
            Set tbl = ws.Range("A1").CurrentRegion
            ws.ListObjects.Add(SourceType:=xlSrcRange, XLListObjectHasHeaders:=xlYes, Source:=tbl).Name = ws.Name
            Range("A1").Select
            Msgbox ActiveCell.Address
        End If
    Next ws
End Sub

Open in new window

Geekamo

ASKER
@Martin Liss,

Hmm, when I run your code - each popup does say $A$1. But none of these worksheets end up having A1 selected.

Any ideas? 
Martin Liss

Why do you believe they are not selected?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Geekamo

ASKER
@Martin Liss,

When I open the workbook:

  1. Accounts (B11 is selected)
  2. Completed (B22 is selected)
  3. Remaining (C15 is selected)
  4. Missing Monthly (J54 is selected)

After I run the code, the same cells are selected.  None of them have cell A1 selected.


Rob Henson

How about using
Range("A1").Activate

Geekamo

ASKER
@Martin Liss,

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Geekamo

ASKER
@Rob Henson,

No, that doesn't work either. :(
Martin Liss

Can you attach your workbook, or a sample workbook that has one sheet of dummy data?
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Geekamo

ASKER
@byundt,

That worked! I used: Application.GoTo ws.Range("A1")

Thank you everyone for your help!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Martin Liss

You have to activate the worksheet before selecting a cell on it.
That doesn't seem to be true in this case.
Geekamo

ASKER
@Martin Liss,

Are you saying that because it was reporting back each A1 reference in the popup?

(All of this is over my head)
byundt

The statement Range("A1").Select only applies to the active worksheet. And you cannot .Select a cell on a non-active worksheet. That’s why I said what I did in my Comment. Application.GoTo is a workaround I frequently use because selecting objects is so slow. 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Sorry byundt and Geekamo, I was wrong.
Geekamo

ASKER
@byundt

When you say selecting an object is so slow...

Are you basically saying,... when the command "Range("A1").Select" is executed - before it even has time to complete that step, it's already moved onto the next line of code - therefor it hasn't technically completed that line of code?

But if the same command is performed on the ACTIVE sheet, there's no speed limitations?

(Sorry, I'm just trying to better understand all this and I have no idea if I'm on track or not)


byundt

VBA code executes synchronously. One statement has to be completed before the VBA run-time engine processes the next statement. Because of that fact, statements that .Select or .Activate objects are going to slow down the macro considerably. "Slow down" may or may not be a problem for your particular macro--that's for you to judge.

Without a qualification preceding it, e.g. ws.Range("A1"), Range("A1") will always refer to the active worksheet. Your original code never changed the active worksheet, so your original macro just selected the same cell (cell A1 on the active worksheet) say a dozen times. That's why you said the code wasn't working.

If you preceded the statement selecting cell A1 with one that selects or activates worksheet ws, then the active worksheet becomes ws and Range("A1").Select selects a cell on that worksheet.

Using Application.GoTo ws.Range("A1") obviates the need to first select or activate worksheet ws and then select cell A1 on that worksheet because it takes the cursor to ws.Range("A1") directly. I frequently use Application.GoTo to return to the original selected cell when writing code that adds worksheets, opens workbooks or does other things that change the active worksheet.
Dim celHome As Range
Set celHome = ActiveCell
'Do stuff that changes the active worksheet
Application.GoTo celHome
End Sub

Open in new window

Martin Liss and Rob Henson are both VBA experts, and very familiar with these issues. They just temporarily forgot about them when viewing the other stuff in your code. That's why Marty posted such a gracious apology, for an oversight I've made myself many times in the past.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rob Henson

The way I understand it, whichever sheet is Active when the code is executed the Range("A1").Select will be performed on that sheet because no other sheet is activated in the code. So Martin's line of code giving a popup with the range selected will always work because it gets selected on the active sheet on the first run through and then doesn't change so appears to work on the following iterations.
Rob Henson

@byundt - looks like our comments were posted at similar times; thanks for the compliment and yes I had missed that point; New Year fog  .🥂🥂
Geekamo

ASKER
@byundt

Thank you very much for the thorough explanation!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.