repeat a macro for all worksheets

Question

Sub Format()

Dim LastRow As Long

Columns("A:E").Select
Range("A3").Activate
Columns("A:E").EntireColumn.AutoFit
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Range("B16").Select

LastRow = Range("D9").End(xlDown).Row
Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"

End Sub

I am working on sheet that require reporting to individuals with different quantity of data tables with same structure. What if I need to repeat the code for all worksheets. I am follower of stackoverflow and learn so many things from this forum.

Thank You Keshav

Possible duplicate of [VBA Excel Macro -How to repeat the macro for each sheet?](https://stackoverflow.com/questions/32316523/vba-excel-macro-how-to-repeat-the-macro-for-each-sheet) — Marcucciboy2, Oct 01 '18 at 12:58

Davesexcel · Accepted Answer · 2018-10-01T17:53:33.607

0

Loop through sheets

Sub Format()

    Dim LastRow As Long, sh As Worksheet

    For Each sh In Sheets
        With sh
            .Columns("A:E").EntireColumn.AutoFit
            .Rows("7:7").Delete Shift:=xlUp
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"
        End With
    Next sh
End Sub

Loop through sheets except one

Sub LoopSheetsExceptOne()
    Dim LastRow As Long, sh As Worksheet, ws As Worksheet

    Set ws = Sheets(1)

    For Each sh In Sheets

        If sh.Name <> ws.Name Then

            With sh
                .Columns("A:E").EntireColumn.AutoFit
                .Rows("7:7").Delete Shift:=xlUp
                LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                .Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"
            End With

        End If

    Next sh

End Sub

edited Oct 01 '18 at 17:53

answered Oct 01 '18 at 12:51

Davesexcel

6,896
2
27
42

What if the same needs to be exclude for Sheet Index 1 – Keshav Oct 01 '18 at 13:16
I have added an additional code to skip the first sheet – Davesexcel Oct 01 '18 at 17:53
Superb :) Thank You Davesexcel – Keshav Oct 02 '18 at 05:24
I am trying to export all worksheets (except Shee1) to pdf to a predefined folder with predefined name. It is saving all the worksheets. The problem is i need to save the excel table till last data row of worksheet to pdf only. – Keshav Oct 02 '18 at 05:56
Sub ExportToPDFsQ() ' PDF Export Macro ' Change C:\Exports\ to your folder path where you need the diles saved ' Save Each Worksheet to a separate PDF file. Dim ws As Worksheet For Each ws In Worksheets ws.Select nm = ws.Name ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\Keshav\Desktop\TSPL\" & nm & Format(Now, " DD-MM-YYYY") & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False Next ws End Sub – Keshav Oct 02 '18 at 06:04
You should start a new thread, the original question has been answered – Davesexcel Oct 02 '18 at 11:27

repeat a macro for all worksheets

1 Answers1

Loop through sheets except one