loop through worksheets not working

try add ws in front of "Range"?

like: ws.Range(....).

The least changes in the your code will be to simply do this..

Sub WorksheetFormatLoop()

Dim ws As Worksheet, columnSum, lastRowInColK, totalRow
    For Each ws In ActiveWorkbook.Worksheets
    'MsgBox ws.Name
ws.select
    ' format order date column
        ws.Columns("D:D").NumberFormat = "mm/dd/yyyy;@"
        
     ' find the last row in column K, go down 2 rows, insert the sum of column K
        Dim LastRowK As Long
        LastRowK = Range("K" & Rows.Count).End(xlUp).Row
        MsgBox LastRowK
        Range("K" & (LastRowK + 2)).Select
        Selection.FormulaR1C1 = "=SUM(R[-" & (LastRowK - 1) & "]C:R[-1]C)"
 
    Next ws

End Sub

Select allOpen in new window

I have just added the ws.select on the top and that will take care of everything since now the worksheet will get active and by default if you don't specify the sheet name it gets run on the sheet which is active or selected..

@ Saurabh

What you are advising is not correct and not a good practice to Select or Activate the Sheet while iterating though the Sheets in the Workbook unless specific circumstances. That is completely unnecessary and will have the adverse affect on the code performance.

How can an experienced person like you suggest something like this to a newbie? That's really strange, how can you do so?

He might not be aware of this at this moment about what you are suggesting him but he may follow the wrong practice in writing the future codes.

@Sktneer,

If you have screenupdating in place which i noticed he didn't so if i have something like this...

Sub WorksheetFormatLoop()
Application.ScreenUpdating = False

Dim ws As Worksheet, columnSum, lastRowInColK, totalRow
    For Each ws In ActiveWorkbook.Worksheets
    'MsgBox ws.Name
ws.Select
    ' format order date column
        ws.Columns("D:D").NumberFormat = "mm/dd/yyyy;@"
        
     ' find the last row in column K, go down 2 rows, insert the sum of column K
        Dim LastRowK As Long
        LastRowK = Range("K" & Rows.Count).End(xlUp).Row
        MsgBox LastRowK
        Range("K" & (LastRowK + 2)).Select
        Selection.FormulaR1C1 = "=SUM(R[-" & (LastRowK - 1) & "]C:R[-1]C)"
 
    Next ws
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Now if you try this code and your code from time performance it will be same thing..Again it won't create that much difference in performance which you can compare it yourself.. Again from a perspective of what is an ideal way to do things..it vary's first from the one who is learning to write the code this one looks most easy for them to comprehend and understand what's really happening..and then one who is really good in understanding the macros what you suggested is the ideal way to write the code..

My main reason for letting him know this approach was to give him an overview of what's really happening here how the macro works and behave because if you notice his code is perfectly well and he understands what is happening but still he doesn't understand why the macro is behaving in a particular manner which is because of the fact that i just made him aware about latter..

I hope this helps to answer your query about things..

Saurabh...

ScreenUpdating being switched off will not save as much time as not selecting sheets.

I would think that the code is unnecessary if an Excel Table was used instead.Formulas & formats copy down automatically and you can have a Total Row that will move as rows are added.

To make managing and analyzing a group of related data easier, you can turn a range of cells into a Microsoft Office Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. See

Overview of Excel Tables

You completely missed the essence of my post.
If you are happy to justify yourself, please be happy.
But in my opinion you have downgraded yourself.

No more argument please!

Thanks.

Roy,

I did some testing arround it..before i posted and enclosed is the testing file on which i tested you can run the sample code for your reference check1 and check2 to see the difference again before you run the second code just clear the formulas..

Sktneer,

Not sure my post on the original code was taken in the manner which i posted as that's the reason i added a clarity about how does the macro works..because my intent was having him that clarity about the same..and i understand your prospective too..about where you are coming from..so in continue i should have one line out their that if you understand the macro code then sktneer is the developer way of writing the code once you are their..again no offense meant to your work..i was just trying to give him another perspective about how macro works..that's it..

Saurabh...
Testing.xlsm

No comment and no more argument please!

Sktneer,

I was arguing..I see your prospective where you are coming from..And i know where exactly i have disappointed you right before i read your first comment..so thats why trying to make you understand my prospective and like i said if i would have added one more line out there that on long term purposes sktneer way of writting is the ideal one that would have address that right and their and we won't be having discussion right now..so my bad on that miss...

Saurabh..

It's ok Saurabh. No issues.

Wow! I never thought my question would stir up such lively debate.  Since my workbook contains more than 100 individual worksheets, I do not want to make each one active as the macro runs.  Thanks for the help and the explanation about why selecting each sheet might not be a good idea.

You're welcome.

I hope, that healthy debate was in your favor and am happy that if you could learn something from that.

Regards.