1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MACRO please help loop

Discussion in 'Business Applications' started by MKilian, Aug 3, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. MKilian

    MKilian Thread Starter

    Joined:
    Aug 3, 2012
    Messages:
    2
    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. :confused:

    Can anyone help me please?


    Thank you :)
     
  2. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    can you attach your workbook so we can see what we are dealing with? remove sensitive data obviously
     
  3. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    sub sheetloop()
    dim ws as worksheet
    for each ws in thisworkbook.worksheets
    [sort code here]
    next
    end sub
     
  4. MKilian

    MKilian Thread Starter

    Joined:
    Aug 3, 2012
    Messages:
    2
    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
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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
     
  7. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    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,
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1063654