Run Time Error 438

[jski21]

Good morning Mr. Excel Team,

I'm placing a macro inside a workbook and trying to create a table but receive a run time error 438; Object doesn't support this property or method. Here the code (bombs off on line 5):

'Convert all data to a Table, Add Table Style, Re-Fit Columns
Sheets("Clockify").Select
Sheets("Clockify").Range("A1").Select
Sheets("Clockify").Range(Selection, Selection.End(xlToRight)).Select
Sheets("Clockify").Range(Selection, Selection.End(xlDown)).Select
Sheets("Clockify").ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = _
"Clockify"
Sheets("Clockify").Range("Table1[#All]").Select
Sheets("Clockify").ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium3"
Sheets("Clockify").Range("A2").Select

I've used similar code in a macro that is housed in my PERSONAL workbook with no issue:

ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium7"


Are there constraints or issues with doing the same when the macro is contained within the actual workbook? If so, why? In working through this I've found I've had to repeatedly select the sheet I'm running the macro on (command button is on another sheet) to get the code to progress.


Thanks in advance, as always, for the instruction and guidance.


jski

 

[Alex Blakenburg]

How about:

ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = _
"Clockify"

 

[jski21]

Yeah tried that already. That gives me a run time error 1004; "The worksheet data for a table needs to be on the same sheet as the table."

 

[Alex Blakenburg]

That might happen if you still had "Sheets("Clockify")" in the line but shouldn't happen otherwise.
Please copy my line in exactly as I have it replacing your line and try it again.

Another option might be:

With Sheets("Clockify")
    .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Clockify"
End With

 

[jski21]

Yes, the first code failed again but the second using With Sheets...did work. I now get the same 438 error on the 2 subsequent lines:

Sheets("Clockify").Range("Table1[#All]").Select
Sheets("Clockify").ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium3"

 

[Alex Blakenburg]

You've named the table "Clockify" not "Table1"
Also you can't select the table on the sheet unless that sheet is active so you either want to make sure its active or not select it since there is no need to.
This should be enough (drop the select line)

Sheets("Clockify").ListObjects("Clockify").TableStyle = "TableStyleMedium3"

 

[jski21]

Thanks Alex. yes, that worked. My error on Clockify vs. Table1 as I had to change the table name for reasons of confidentiality.

This is helpful. So even though the command button is on another sheet, I can just activate the sheet I want to transform rather than select it?

 

[jski21]

I'm having a bit of difficulty with the distinctions of running code from the PERSONAL file as opposed to running it inside the actual workbook. I would think you could just copy/paste the code and all should be good but that is not the case.

This excerpt worked from the PERSONAL file but the text to columns now gives me a 1004 error; Method 'Range' of object '_Worksheet' failed:

'Perform Text to Columns on column G to format dates
Sheets("Clockify").Columns("G:G").Select
Selection.TextToColumns Destination:=Range( _
"Clockify[[#Headers],[Start Date]]"), DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 3) _
, TrailingMinusNumbers:=True

 

[Alex Blakenburg]

When you put a macro in the same workbook as the data you normally explicitly tell it the workbook, the worksheet and the range you want to run it on.
Normally when you put it in the personal workbook you want to be able to run it on multiple workbooks/worksheets/ranges so you tend to use ActiveWorkbook, ActiveSheet & Selection.

The code you have will assume the activeworkbook contains the sheet your are naming Clockify.
The Columns select statement is assuming that Clockify is already the active sheet.

Your error though is more indicative that Clockify[[#Headers],[Start Date]] is invalid.
In a cell outside the table enter = and then click on your heading Start Date. It will return a structured reference to that cell. Check that is exactly the same as what you have in the code.