Loop Though all worksheets in work book

[Cooky857]

HI all

i know I'm missing something very small but i'm lost, i have the below code that i need to loop though every worksheet in the workbook, but it only works on the worksheet i'm on what am i missing Please

Sub Copy()

Dim WB As Workbook

Dim ws As Worksheet

For Each ws In Worksheets

     
 Range("A1:L2").Copy
  
 Range("A6").PasteSpecial Transpose:=True
 
Next ws

End Sub

 

[Joe4]

Looping through the worksheets does NOT select/activate them. You need to add sheet references to your ranges, i.e.

Sub Copy()

Dim WB As Workbook

Dim ws As Worksheet

For Each ws In Worksheets

     
 ws.Range("A1:L2").Copy
  
 ws.Range("A6").PasteSpecial Transpose:=True
 
Next ws

End Sub

 

[Cooky857]

Looping through the worksheets does NOT select/activate them. You need to add sheet references to your ranges, i.e.

Sub Copy()

Dim WB As Workbook

Dim ws As Worksheet

For Each ws In Worksheets

   
 ws.Range("A1:L2").Copy
 
 ws.Range("A6").PasteSpecial Transpose:=True
 
Next ws

End Sub[/CODE

thank you so much cant believe i missed that relay helpful thanks

 

[Joe4]

You are welcome.

Also, you should not use reserved words like "Copy" as the names of variables, procedures, or functions.
Doing that can cause unexpected behavior or errors.