Macro - Adapting a Table's name every time the macro runs

[Jeeremy7]

Hello everyone,

So every month we export data and this data was always sorted a editited by hand.
I wanted to create a macro to automate it because it's always the same type of modification.
The problem is that we would have 1 tab per month in excel.

When I ran the original macro I created a table and just changed it's layout color.
The problem is that when I ran the same macro in an other tab, I have an error because the "table1" is now "table2".

Is there a way that the macro would not select the table name but the table in the active sheet instead ?

Thanks a lot !

 

[Fluff]

If you only have one table per sheet you can use something like

Sheets("Sheet1").ListObjects(1).TableStyle = "TableStyleMedium7"

 

[Jeeremy7]

If you only have one table per sheet you can use something like

Sheets("Sheet1").ListObjects(1).TableStyle = "TableStyleMedium7"

Can I use the activesheet instead or it wouldn't work ?
I would have the same button in each month tab so the sheet would not always be the same name

Thanks !

 

[Fluff]

Can I use the activesheet instead

Yes.

 

[Jeeremy7]

Yes.

Now I have the same problem with the part when I insert a slicer. I tried modifying the "Table" by 1 again but not working :/
Does the 1 means "the first table you see" ?

 

[Fluff]

It's the first table in the listobjects collection on that sheet.

 

[Jeeremy7]

Ok the problem is I think when doing a slicer it wrote ActiveWorkbook but when I modify to ActiveSheet it doesn't work

 

[Fluff]

I've never used slicers, so unfortunately cannot help with that.

 

[Jeeremy7]

That's ok thanks anyways !

 

[Fluff]

You're welcome.