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
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)
You have to activate the worksheet before selecting a cell on it.That doesn't seem to be true in this case.
Dim celHome As Range
Set celHome = ActiveCell
'Do stuff that changes the active worksheet
Application.GoTo celHome
End Sub
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.
Open in new window