Loop through sheets problem

[bujaman]

I am trying to loop through all the sheets in my workbook and rearrange some data. So far I have been unsucessful for some reason. I think I have the loop and everything set up correctly, but the macro gets hung up on one sheet and executes the code over and over again on the same sheet without looping. Here is what I have:

Sub DBOrderIt()Application.ScreenUpdating = False


Dim TblRng As Range
Dim topcell As Range
Dim bottcell As Range
Dim ws As Worksheet


For Each ws In Worksheets


Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
Selection.Cut Destination:=Range("B2")
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range("A65536").End(xlUp).Row)
Range("B2:B" & Range("A65536").End(xlUp).Row).Select




Set TblRng = Range("D1:X1")


For Each cell In TblRng
On Error Resume Next


Range(cell.Offset(1, 0), cell.Offset(Range("A65536").End(xlUp).Row, 0)).Copy
Range("C" & Range("C65536").End(xlUp).Row + 1).Select
ActiveSheet.Paste
cell.Copy
Range("B" & Range("B65536").End(xlUp).Row + 1).Select
ActiveSheet.Paste
'AutoFill Here
Selection.AutoFill Destination:=Range("B" & (Range("B65536").End(xlUp).Row) & ":B" & (Range("C65536").End(xlUp).Row))




Next cell




Next ws
Application.ScreenUpdating = True


End Sub

I really can't figure out why it won't loop through the sheets, so any help would be awesome, thanks!

 

[andrewkard]

Hello. You need to activate sheet:
For Each ws In Worksheets
ws.activate

 

[bujaman]

Awesome! Thanks for the quick reply, that is what I needed!

 

[Marcelo Branco]

Hi,

As the code loops through all sheets i think you should qualify all the ranges. Something like (UNTESTED)

.....
For each ws in Worksheets
[COLOR=#ff0000]With wk[/COLOR]
    [COLOR=#ff0000].[/COLOR]Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   [COLOR=#ff0000].[/COLOR]Range("C1").Select
   Selection.Cut Destination:=[COLOR=#ff0000].[/COLOR][COLOR=#000000]Ra[/COLOR]nge("B2")
  'and so on
   ....
   ....
[COLOR=#ff0000]End with[/COLOR]
Next ws

M.