davidtaylor598
Board Regular
- Joined
- Oct 5, 2010
- Messages
- 84
Hi all,
I have X amount of worksheets, all with the same column headings (created with a macro below). I need to hide certain columns (C,D, E) and re-order the remaining ones to A, F,B. Is there a quick way to do this? I guess it will have to be somewhere inside the loop below.
I have X amount of worksheets, all with the same column headings (created with a macro below). I need to hide certain columns (C,D, E) and re-order the remaining ones to A, F,B. Is there a quick way to do this? I guess it will have to be somewhere inside the loop below.
Code:
Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("C2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("C" & i).Value <> .Range("C" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("C" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub