Loop through all sheets in workbook

Question

I have an Excel file with 100 sheets, all with the same layout.

I need to loop through all sheets to apply formatting.

Dim ws As Worksheet 
For Each ws In ThisWorkbook.Worksheets
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Balance Sheet"         
    Range("Q1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Cash Flow"
Next ws
End Sub

Have a look at: https://stackoverflow.com/q/50776026/4961700 — Solar Mike, Jun 18 '22 at 13:23
MS already have an article [about it](https://support.microsoft.com/en-us/topic/macro-to-loop-through-all-worksheets-in-a-workbook-feef14e3-97cf-00e2-538b-5da40186e2b0) Does that work for you? — rkosegi, Jun 18 '22 at 13:56
What format do you want to apply? Why do you loop through the worksheets and don't use the loop varaible `ws ` like `ws.range("A1").FormulaR1C1 = "Balance Sheet"`? `Application.CutCopyMode = False` and `Select`is not neccessary at all. Sorry to be honest, but _Can you help me with this ?_ is not a question which one can answer in a satisfying way for you. — Storax, Jun 18 '22 at 14:45
Your ranges are unqualified, so they assume the active sheet. Add `ws` to qualify them. `ws.Range("A1").Select` - do not use `Select` or `ActiveCell` . — braX, Jun 18 '22 at 18:30

score 1 · Accepted Answer · answered Jun 18 '22 at 22:57

1

Replace that with:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Range("A1").Value = "Balance Sheet"
    ws.Range("Q1").Value = "Cash Flow"
    Application.CutCopyMode = False
Next ws

answered Jun 18 '22 at 22:57

Bryan

48
3

Loop through all sheets in workbook

1 Answers1