VBA Run-Time Error '1004': Application-defined or object-defined error. What am I doing wrong?

Question

I have a Powershell script that will generate a CSV file with all available storage space for 18+ servers. I am now trying to automatically format the CSV file so it's a lot nicer to look at. My problem is that my code runs successfully once but then throws the 1004 error on a second run.

My code looked a lot more choppy and less polished prior to coming to StackOverflow. I've now removed some other mistakes like using Activesheet unnecessarily, using .Select, etc, but I now still cannot find the issue.

Please see my code below (please note the AutoFit part that I've commented out is a part I'm yet to get working).

Sub A_AllToTable()
    Dim tbl As ListObject
    With Sheets(1)
        Set tbl = .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes)
        tbl.TableStyle = "TableStyleDark11"
'        tbl.Columns.AutoFit
    End With
End Sub

When I open the Workbook from fresh and run the code, it runs as expected. It will format the table. I'm hoping to get it to AutoFit as well as automatically sort by size, but for now I just want the code to work more than once

Could someone please provide some insight into why this isn't working so I can avoid making the same mistake again in the future?

When you say it does not run twice, you mean you call the sub twice in a different sub? Or do you execute the sub once and when you execute it again an error is thrown? Also in what line does the error occur? — Nils
– Nils, Commented Aug 15, 2019 at 7:46
My apologies. When I run it straight from the VBA console for a second time it does not work. Also, it does not provide any information as to which line it is occurring on. — Ed-
– Ed-, Commented Aug 15, 2019 at 7:47
What is confusing me a bit is that Sheets(1) is not defined, did you declare it somewhere? I mean something like Set Sheets = ThisWorkbook.Worksheets? Also have you checked your variables in the local window? That might give you some hint about the error and where it occurs. — Nils
– Nils, Commented Aug 15, 2019 at 7:51
Would it be possible that Sheet1 somehow changes name after the 1st run, meaning the macro can't find it again when you decide to re-run? — ciuncky
– ciuncky, Commented Aug 15, 2019 at 7:54
@Nils: I think you mean Set wks = ThisWorkbook.Worksheets(1) or Set wks = ThisWorkbook.Worksheets("Sheet1") for example.... — Siddharth Rout
– Siddharth Rout, Commented Aug 15, 2019 at 8:07

Ed- · Accepted Answer · 2019-08-15 08:51:39Z

1

Eureka! It turns out it wasn't running as it's trying to create another table on top of the table that already exists. I added some code to first ensure the workbook is clear of formatting and tables, now no errors occur!

Thank you to everyone who has been seeking more information on the question. You've all been a great help!

Final Code:

Sub A_AllToTable()
    Sheet1.ListObjects(1).Unlist
    Sheet1.UsedRange.ClearFormats
    Dim tbl As ListObject
    Set wks = ThisWorkbook.Worksheets(1)
    With wks
        Set tbl = wks.ListObjects.Add(xlSrcRange, wks.UsedRange, , xlYes)
        tbl.TableStyle = "TableStyleDark11"
'        tbl.Columns.AutoFit
    End With
End Sub

answered Aug 15, 2019 at 8:51

Ed-

569 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

++ Good Work :) BTW You can also say Sheet1.Cells.Clear instead of those 2 lines :)

Collectives™ on Stack Overflow

VBA Run-Time Error '1004': Application-defined or object-defined error. What am I doing wrong?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related