Create Tables in VBA

[Hatch10]

I have an Excel Workbook downloaded from an Internet site. It has only one sheet with 50 suppliers listed on it. Each supplier is shown with a header row and the next row shows the shipping parameters in 10 columns. I need a VBA code for Excel 10 to loop through the sheet and create tables and rename the table for each shipper. I can record a macro that works fine, but the shippers change over time then the macro will not work. When recording the macro I click on the first column in the row showing the parameters (Shipper ID) and Excel knows the range for the table. I am new to VBA and trying to learn, any help is appreciated.

 

[Jerry Sullivan]

Are you still getting an "Object Variable or With Block Variable Not Set" error, or did removing the redundant add sheet fix that?

 

[Hatch10]

Yes, I am still getting the error.

 

[Jerry Sullivan]

I'd be glad to take a look at your file with any confidential data removed/changed.

You can upload it to a hosting site like Box.com and post a link or send me a PM and we'll exchange email addresses.

 

[Hatch10]

Thanks, I appreciate that. Now that I take a closer look at what the program is doing I see that it is not trying to recreate the Bailey table after all. It is making Bailey the last table before I get the error. That is curious because Bailey is the first on the active sheet. As I said the shippers are in alphabetical order and each shipper has a unique name.

I am in a hotel room with insecure wifi. When I get home tonight I will send a private message and if you give me your e-mail address I will send the file.

Thanks so much for your help. I believe I may owe you several drinks.

 

[Jerry Sullivan]

Thanks for sending your example file.
I don't get any errors when I run the code resulting from Posts #2, #14 and #17.

I've sent you the file with that code by email for you to try. Please let me know if you still get the error using that file.

If you still get the error, what version of Excel are you using?
Your file has some merged cells which could possibly be causing the problem if you are using a different version of Excel than mine.

 

[Hatch10]

I am using Excel 10. I do not get the error now. Thanks!