Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

MACRO please help loop


(!)

MKilian's Avatar
MKilian   (Mariska) MKilian is offline
Computer Specs
Member with 2 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
03-Aug-2012, 05:23 AM #1
MACRO please help loop
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's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
03-Aug-2012, 09:46 AM #2
can you attach your workbook so we can see what we are dealing with? remove sensitive data obviously
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 453 posts.
 
Join Date: Apr 2012
Experience: Intermediate
05-Aug-2012, 09:48 PM #3
sub sheetloop()
dim ws as worksheet
for each ws in thisworkbook.worksheets
[sort code here]
next
end sub
MKilian's Avatar
MKilian   (Mariska) MKilian is offline
Computer Specs
Member with 2 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
06-Aug-2012, 02:55 AM #4
Question Stuck on error!
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's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,190 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
06-Aug-2012, 09:47 AM #5
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's Avatar
Garf13LD Garf13LD is offline
Member with 453 posts.
 
Join Date: Apr 2012
Experience: Intermediate
06-Aug-2012, 12:08 PM #6
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's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
06-Aug-2012, 12:12 PM #7
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,
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑