loop through worksheets not working

Greetings, Experts!

I am trying to write an Excel macro that does the following 2 things for each sheet in the currently open workbook:
- format column D, which is a date column
- total up column K, regardless of how many rows there are

Here is my code:
Sub WorksheetFormatLoop()

Dim ws As Worksheet, columnSum, lastRowInColK, totalRow
    For Each ws In ActiveWorkbook.Worksheets
    MsgBox ws.Name
    ' 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

Open in new window


The date formatting is working fine but the totaling of column K is not working.  I'm getting a total, but it's happening only on the first worksheet.  So, for example, if my workbook has 5 worksheets in it, This code inserts the sum of column K 5 times on the first worksheet and not at all on the other 4 worksheets.  

This is driving me nuts. Msgbox tells me the name of each sheet and the date formatting in column D is happening on each worksheet.  Can someone please tell me what I've missed?

Many thanks!
monkeybiz12345Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try add ws in front of "Range"?

like: ws.Range(....).
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It should be like this.....
Sub WorksheetFormatLoop()

Dim ws As Worksheet, columnSum, lastRowInColK, totalRow
    For Each ws In ActiveWorkbook.Worksheets
    MsgBox ws.Name
    ' 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 = ws.Range("K" & Rows.Count).End(xlUp).Row
        MsgBox LastRowK
        ws.Range("K" & (LastRowK + 2)).FormulaR1C1 = "=SUM(R[-" & (LastRowK - 1) & "]C:R[-1]C)" 
    Next ws
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
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

Open 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..
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ 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.
1
Saurabh Singh TeotiaCommented:
@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

Open 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...
0
Roy CoxGroup Finance ManagerCommented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Saurabh Singh TeotiaCommented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No comment and no more argument please!
0
Saurabh Singh TeotiaCommented:
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..
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It's ok Saurabh. No issues.
0
monkeybiz12345Author Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome.

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

Regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.