Sort all worksheets in a workbook
I have this code that actually does exactly what I want:
goes to a specific range, copies the table to a new range, and reorders the table.
Here is the code:
Sub copy_paste_sort()
Dim oneRange As Range
Dim aCell As Range
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Set oneRange = Range("CZ269:DA294")
Set aCell = Range("DA269")
Range("CW269:CX294").Select
Selection.Copy
Range("CZ269:DA294").Select
ActiveSheet.Paste
oneRange.Sort Key1:=aCell, Order1:=xlDescending, Header:=xlNo
Next I
End Sub
The problem is that the code doesn't work in all sheets, it repeats n times on the first sheet.
source to share
You will never tell the code to go to the next worksheet. Also, your choices don't seem necessary.
Sub copy_paste_sort()
Dim oneRange As Range
Dim aCell As Range
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Set oneRange = worksheets(I).Range("CZ269:DA294")
Set aCell = worksheets(I).Range("DA269")
worksheets(I).Range("CW269:CX294").Copy worksheets(I).Range("CZ269:DA294")
oneRange.Sort Key1:=aCell, Order1:=xlDescending, Header:=xlNo
Next I
End Sub
Also, since you are sorting the range, I am assuming that it contains values, not formulas. If so, you can replace
worksheets(I).Range("CW269:CX294").Copy worksheets(I).Range("CZ269:DA294")
from
worksheets(I).Range("CZ269:DA294").Value = worksheets(I).Range("CW269:CX294").Value
The advantage of this is that it is slightly faster and does not use your clipboard.
source to share
Try like,
Sub copy_paste_sort()
Dim I As Long
' Begin the loop.
For I = 1 To ActiveWorkbook.Worksheets.Count
With Worksheets(I)
With .Range("CW269:CX294")
.Copy destination:=.Range("CZ269")
.Offset(0, 3).Cells.Sort Key1:=aCell, Order1:=xlDescending, Header:=xlNo
End With
End With
Next I
End Sub
You went through I
as the worksheet index number but didn't do anything with it. With Worksheets(I) ... End With
links to worksheet eac h in turn. Note what Range(...)
becomes .Range(...)
, so that he knows which worksheet is his parent.
source to share