Run macro in whole workbook

DOF2001

Active Member
Joined
Jan 28, 2005
Messages
310
HI! to all

I have this macro that runs great but i want to make it work in the whole workbook besides the first 2 sheets, does any one has a quic solution for this, i will appreciate.


Thanks!

Sub Curves_Transpose_Part1()

Rows("1:1").Select
Selection.Insert Shift:=xlDown

Dim a1 As Range, rng0
Set rng0 = Range("Q1:CE1")
For Each a1 In rng0
If a1.Offset(1, 0) > " " Then
a1.FormulaR1C1 = "=R[1]C[1]-R[1]C-1"
End If
Next a1

Dim c As Range, Rng
Set Rng = Range("Q1:CE1")

For Each c In Rng
Select Case c
Case Is = 1:
c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
c.Offset(1, 1).FormulaR1C1 = "a"
Case Is = 2:
c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
c.Offset(1, 1).FormulaR1C1 = "b"
c.Offset(1, 2).FormulaR1C1 = "b"

End Select
Next c
Rows("1:1").Select
Selection.Delete Shift:=xlUp

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Do you mean run the same code on each worksheet?
Code:
Sub Curves_Transpose_Part1()

Dim ws As Worksheet
Dim a1 As Range, rng0 As Range
Dim c As Range, Rng As Range

    For Each ws In Worksheets
        With ws
            .Rows("1:1").Insert Shift:=xlDown
            Set rng0 = .Range("Q1:CE1")
            For Each a1 In rng0
                If a1.Offset(1, 0) > " " Then
                    a1.FormulaR1C1 = "=R[1]C[1]-R[1]C-1"
                End If
            Next a1
    
            Set Rng = .Range("Q1:CE1")
    
            For Each c In Rng
                Select Case c
                    Case Is = 1:
                        c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
                        c.Offset(1, 1) = "a"
                    Case Is = 2:
                        c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
                        c.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
                        c.Offset(1, 1) = "b"
                        c.Offset(1, 2) = "b"
                End Select
            Next c
            .Rows("1:1").Delete Shift:=xlUp
        End With
    Next ws
End Sub
 

Forum statistics

Threads
1,140,937
Messages
5,703,256
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top