Need a macro optimization to include last row with data

[iliasark]

Dear Forum,

As per below, I am running a simple macro which has some table/row/column/margin formats and a sum at the end-below the last cell of the column with the numbers.
My problem is that every time I need to run the macro the range of rows is different and the table format keeps the initial A1:K127 range, which also affects the sum cell. When recorded the macro I hit (Ctrl+Shift+arrows) but the macro does not consider it (to reach this way automatically all the range), can you please give me an advise on how to make it every time to consider the new range of rows?

---------------------------------------------------

Please Login or Register  to view this content.

-------------------------------------------------------------

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

---------- Post added at 07:25 PM ---------- Previous post was at 07:25 PM ----------

Try this

Please Login or Register  to view this content.

[iliasark]

Thank you for notifying, well noted.

[arlu1201]

Did you try the code?

[iliasark]

Please Login or Register  to view this content.

[arlu1201]

I had corrected the code for you and given you my version (see below). Did you try it?

Please Login or Register  to view this content.

[iliasark]

I will try it tomorrow and let you know, I might need to re-post the whole macro within the code tags.thank you for helping out, I am new on vba/macros..

[iliasark]

Hello Arlette,

Please see below the whole initial macro. Can you please do your above amendments into this one? thank you in advance

Please Login or Register  to view this content.

[arlu1201]

Try this

Please Login or Register  to view this content.

[iliasark]

Tried and I get errors (with debug option and highlighted in yellow).
1.

Please Login or Register  to view this content.

The 'Sheet1' has always a name in the excel sheet, to bypass it i renamed it in the code. (is there a way to make it every time get the sheet's name by default cause every day it changes?)

2.

Please Login or Register  to view this content.

It seems that it does not accept the table format?how can I correct this?

[arlu1201]

I had not tested the code since i would have to replicate the sheet.

What do you want the name for Sheet1? Since it changes often, will it be the first sheet in your file? In that case, you can try

Please Login or Register  to view this content.

"TableStyleMedium2"

Try removing the "" on either side.

[iliasark]

You are right, let me attach the xls. file so you may help me more easily.
*note that the sheet name is always the first but every day changes name (actually the last 4 digits).

[iliasark]

Hello Arlette,

Can you please see my attached workbook? I have included in the workbook the macro I'm using, which is 'imperfect' because I'm forced to make the table till the 200th row so as to include every time all the data (they usually range between 50 and 180 rows)for the sum. Please have a look and advise how can I instruct excel to create the Sum under the last row which varies every time.

Thank you very much
ilias