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

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

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

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

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

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