Batch formatting ranges into tables with structured custom names

[gilliamwibson]

Hi,

Due to time constraints I need to troubleshoot the following code. Im a complete nub, so please help!
I have 30 workbooks with 30 worksheets inside each on average. If my workbooks are named A,B,C..Z
then my worksheets are A1, A2, A3....
In each worksheet there are two tables (Attributes and Payments) which I'd like to convert into excel tables. The tables are located in the exact same ranges in every single worksheet.
The tables are basically forms. I am trying to format those ranges by giving them custom names and not the ones auto-generated by excel. so for instance
in Workboook A the first three worksheets, following (TableName="Pmt_" & WSName) rule, will have tables named like:
worksheet A1: Att_A1, Pmt_A1
worksheet A2: Att_A2, Pmt_A2
worksheet A3: Att_A3, Pmt_A3

The code below lets me chose a range of worksheet names (a range in fact from which the all of the tabs were created from) and then goes through the names one by one activating the sheets and converting the ranges into tables.

But my code seems to be stuck right at the start where it is supposed to first activate the sheet whose name is next in the list.

Any thoughts? THANK YOU IN ADVANCE

[dflak]

The macro recorder does a good job getting the syntax down, but does a poor job of navigating. Generally speaking, there is no need to select or activate anything, and if you ever see code like this:

Please Login or Register  to view this content.

It can almost always be replaced with

Please Login or Register  to view this content.

Also, your idea of what the active sheet or active cell is may be different than the code's idea of what these values are. A lot depends on where you are when you start recording your macro.

The following code looks at every sheet in the workbook and checks to see if it starts with the letter "A." If it does, then it makes the two tables you want.

Please Login or Register  to view this content.

[gilliamwibson]

Wow thank you very much.

My attach link doesn't work so I couldn't attach the spreadsheet. Is it possible I just select all the sheet names that I need the code to work on from the list they were created. (It is one short list as I said consisting of 30 entries on avg.) Have excel keep it as a string ("Selection", hence my original code) and instead of changing the code for each file every time I wanna run it I'd just make a new selection every time. Testing this now. But I hope you have time to consider my question.

Kind regards

[dflak]

Use a table to store the names of the sheets you want to process. Take a look at the attached, You will see that the code is almost identical.

[gilliamwibson]

Thank you a lot. I sent a thank you earlier I must have messed up something it didn't go through! I tweaked the code to work with manual selection of tab names since as I said I created those tabs from a list.. son instead of naming lists in every file and I had 23 of them I just used Selection.
Thank you again!!! worked like a charm..