macro to execute code on all worksheets in workbook
 

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub

macro to execute code on all worksheets in workbook
 

Try something like this

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
sh.Columns("B:C").Select
sh.Range("B16").Activate
Selection.UnMerge
sh.Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
sh.Cells.Select
sh.Range("B1").Activate
Selection.EntireColumn.Hidden = False
sh.Columns("B:C").Select
Selection.Insert Shift:=xlToRight
sh.Range("D3").Select
Selection.Copy
sh.Range("B5").Select
ActiveSheet.Paste
sh.Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
sh.Range("C6").Select
ActiveSheet.Paste 'Is this Activesheet or sh?
Next sh

I'd step through it line by line to ensure it's doing what you want.



"Gretta" wrote:

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub

macro to execute code on all worksheets in workbook
 

You are already looping through the worksheets in the workbook. So, without
looking much at what the code is doing inside the loop, I think a quick fix
would be to activate the sheet before executing the code inside the loop. In
the code below, the only line that I added is the one that says "sh.Activate".



Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
sh.Activate 'Activate the sheet
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub



--
Hope that helps.

Vergel Adriano


"Gretta" wrote:

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub

macro to execute code on all worksheets in workbook
 

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
sh.select '<-- added

Might work.



Gretta wrote:

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub

--

Dave Peterson

macro to execute code on all worksheets in workbook
 

Alternatively, you could use

for each sh in activeworkbook.worksheets
sh.activate
...


"Gretta" wrote:

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub

macro to execute code on all worksheets in workbook
 

Thank you to all!

Sh.Activate worked great!

/grt

"Barb Reinhardt" wrote:

Alternatively, you could use

for each sh in activeworkbook.worksheets
sh.activate
...


"Gretta" wrote:

I am trying to execute the following code on multiple sheets in my workbook.
However, when I run the macro, it only executes on the active sheet. How do
I pass the focus to each next sheet? Something is missing...

Thanks!
Gretta

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
Columns("B:C").Select
Range("B16").Activate
Selection.Unmerge
Columns("B:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1").Activate
Selection.EntireColumn.Hidden = False
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Next sh
Application.ScreenUpdating = True
End Sub