[MKilian]

Hi,

I wrote a macro and in it I need to do a sort of data on all the tabs. if there is a tab that doesn't have any data to sort, I need the macro to move on to the next tab. I think it needs to be done with a loop but I have no idea how a loop works.

Can anyone help me please?


Thank you

[DataBase]

can you attach your workbook so we can see what we are dealing with? remove sensitive data obviously

[Garf13LD]

sub sheetloop()
dim ws as worksheet
for each ws in thisworkbook.worksheets
[sort code here]
next
end sub

[MKilian]

Please note that I am not a programmer and I'm just starting the macro thing so if you see anything in here that can help me I would really appreciate it. I came up with a solution to my problem but have no idea if it is the right way to do it although it worked. I will highlight the part where I got stuck.

Sub BLM()
'
' BLM Macro
'
'

Application.ScreenUpdating = False

Workbooks.Open Filename:= _
"C:\Users\markil\Desktop\CELLPHONE REGIONAL\MONTHLY ACCOUNTS\BLM CELLPHONES.xls"

Range("A1").Select
Selection.End(xlDown).Select
LAST = ActiveCell.Row

Sheets("CALLS").Select
ActiveWorkbook.Worksheets("CALLS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CALLS").Sort.SortFields.Add Key:=Range("B2:B" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("CALLS").Sort.SortFields.Add Key:=Range("I2:I" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("CALLS").Sort.SortFields.Add Key:=Range("A2:A" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("CALLS").Sort
.SetRange Range("A1:J" & LAST)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Range("A1").Select
Selection.Subtotal GroupBy:=9, Function:=xlCount, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.AutoFilter
Selection.AutoFilter
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
On Error GoTo 0

Sheets("DATA").Select
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("B2:B" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I2:I" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("A2:A" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATA").Sort
.SetRange Range("A1:J" & LAST)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Range("A1").Select
Selection.Subtotal GroupBy:=9, Function:=xlCount, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.AutoFilter
Selection.AutoFilter
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
On Error GoTo 0

Sheets("SMS").Select
ActiveWorkbook.Worksheets("SMS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SMS").Sort.SortFields.Add Key:=Range("B2:B" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SMS").Sort.SortFields.Add Key:=Range("I2:I" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SMS").Sort.SortFields.Add Key:=Range("A2:A" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SMS").Sort
.SetRange Range("A1:J" & LAST)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Range("A1").Select
Selection.Subtotal GroupBy:=9, Function:=xlCount, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.AutoFilter
Selection.AutoFilter
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
On Error GoTo 0

Sheets("MMS").Select
ActiveWorkbook.Worksheets("MMS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MMS").Sort.SortFields.Add Key:=Range("B2:B" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("MMS").Sort.SortFields.Add Key:=Range("I2:I" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("MMS").Sort.SortFields.Add Key:=Range("A2:A" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MMS").Sort
.SetRange Range("A1:J" & LAST)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
On Error Resume Next
Selection.Subtotal GroupBy:=9, Function:=xlCount, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.AutoFilter
Selection.AutoFilter
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
On Error GoTo 0

Sheets("CALLS").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE OF CALL"
Range("B1").Select
ActiveCell.FormulaR1C1 = "FROM NUMBER"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PHONE BELONGS TO"
Range("D1").Select
ActiveCell.FormulaR1C1 = "REGION"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TIPE"
Range("F1").Select
ActiveCell.FormulaR1C1 = "TIME CALLED"
Range("G1").Select
ActiveCell.FormulaR1C1 = "DURATION"
Range("H1").Select
ActiveCell.FormulaR1C1 = "NUMBER CALLED"
Range("I1").Select
ActiveCell.FormulaR1C1 = "MATCH"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Sheets("DATA").Select
ActiveCell.FormulaR1C1 = "DATE DATA USAGE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "FROM NUMBER"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PHONE BELONGS TO"
Range("D1").Select
ActiveCell.FormulaR1C1 = "REGION"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TIPE"
Range("F1").Select
ActiveCell.FormulaR1C1 = "TOTAL DATA"
Range("G1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Range("H1").Select
ActiveCell.FormulaR1C1 = "DATA TO NUMBER"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Sheets("SMS").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE OF SMS"
Range("B1").Select
ActiveCell.FormulaR1C1 = "FROM NUMBER"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PHONE BELONGS TO"
Range("D1").Select
ActiveCell.FormulaR1C1 = "REGION"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TIPE"
Range("F1").Select
ActiveCell.FormulaR1C1 = "TIME OF SMS"
Range("G1").Select
ActiveCell.FormulaR1C1 = "SMS TO"
Range("H1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Range("I1").Select
ActiveCell.FormulaR1C1 = "MATCH"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select


Sheets("MMS").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE OF MMS"
Range("B1").Select
ActiveCell.FormulaR1C1 = "FROM NUMBER"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PHONE BELONGS TO"
Range("D1").Select
ActiveCell.FormulaR1C1 = "REGION"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TIPE"
Range("F1").Select
ActiveCell.FormulaR1C1 = "TIME OF MMS"
Range("G1").Select
ActiveCell.FormulaR1C1 = "DATA USAGE"
Range("H1").Select
ActiveCell.FormulaR1C1 = "MMS TO"
Range("I1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

[Keebellah]

Hi Mariska,
Nice code etc etc, but I think you're better off if you post a file with the code included.
Seeing the code I assume it's Excel version 2007 or 2010 but you should mention this too to not allow for misinterpretations

[Garf13LD]

Option Explicit

Sub BLM()
'
' BLM Macro
'
' 'always declare your variables
Dim wb As Workbook
Dim ws As Worksheet
Dim LAST As Long
Application.ScreenUpdating = False

'dim a wb object will ensure that you know exactly which workbook you are refering to.
'activeworkbook can change if you cross reference
Set wb = Workbooks.Open(Filename:= _
"C:\Users\markil\Desktop\CELLPHONE REGIONAL\MONTHLY ACCOUNTS\BLM CELLPHONES.xls")

For Each ws In wb.Worksheets
'if you have sheets to exclude, encase the for next with if so it only run on selected sheets.
LAST = ws.UsedRange.Rows.Count
With ws
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B2:B" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("I2:I" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("A2:A" & LAST) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange ws.Range("A1:J" & LAST)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'your possible problem might be between the on errors
'if your list is empty that is
On Error Resume Next
.Range("A1").Subtotal GroupBy:=9, Function:=xlCount, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
On Error GoTo 0

'use of select case to separate codes to run on each sheet.
Select Case ws.Name
Case "CALLS"
.Columns("J:J").Delete
.Range("A1") = "DATE OF CALL"
.Range("B1") = "FROM NUMBER"
.Range("C1") = "PHONE BELONGS TO"
.Range("D1") = "REGION"
.Range("E1") = "TIPE"
.Range("F1") = "TIME CALLED"
.Range("G1") = "DURATION"
.Range("H1") = "NUMBER CALLED"
.Range("I1") = "MATCH"
Case "DATA"
.Columns("I:J").Delete
.Range("A1") = "DATE DATA USAGE"
.Range("B1") = "FROM NUMBER"
.Range("C1") = "PHONE BELONGS TO"
.Range("D1") = "REGION"
.Range("E1") = "TIPE"
.Range("F1") = "TOTAL DATA"
.Range("G1") = "DESCRIPTION"
.Range("H1") = "DATA TO NUMBER"
Case "SMS"
.Columns("G:G").Delete
.Range("A1") = "DATE OF SMS"
.Range("B1") = "FROM NUMBER"
.Range("C1") = "PHONE BELONGS TO"
.Range("D1") = "REGION"
.Range("E1") = "TIPE"
.Range("F1") = "TIME OF SMS"
.Range("G1") = "SMS TO"
.Range("H1") = "DESCRIPTION"
.Range("I1") = "MATCH"
Case "MMS"
.Columns("J:J").Delete
.Range("A1") = "DATE OF MMS"
.Range("B1") = "FROM NUMBER"
.Range("C1") = "PHONE BELONGS TO"
.Range("D1") = "REGION"
.Range("E1") = "TIPE"
.Range("F1") = "TIME OF MMS"
.Range("G1") = "DATA USAGE"
.Range("H1") = "MMS TO"
.Range("I1") = "DESCRIPTION"
End Select
.Cells.EntireColumn.AutoFit
End With
Next
wb.Save
wb.Close
End Sub

[DataBase]

Garf13LD,

when replying you will see a button at the bottom called 'Go Advanced' click on this button and you will be taken to a new window, from here you will have many new options, one of them will allow you to attach a file to your post, it looks like a paper clip, please attach you file, removing and sensitive data of course, and we will try to help.

thanks,