Results 1 to 11 of 11

Very long macro - Thanks

Threaded View

  1. #1
    Registered User
    Join Date
    07-20-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    63

    Unhappy Very long macro - Thanks

    Hi All

    I used excel for year but only to do the simple things. I'm now getting into macro's, i have this macro that works but it takes an age to run and the screen flickering does my head in.
    I have a sheet call Bene, which in col BO with have CAT , A,B,C,D,E,F,G or H. It will then move all the A to Bene Cat A.

    I have only put this one on as an example but i have the same thing for UK,IBE,FRA,FIN
    and all have 8 sheets to go with them. I have used the same code for all macros and i've even done a macro that does them all together using the same code.

    Can someone please help me in making my macro more efficent
    Sub BENECAT()
     'BENE
    'CAT A
    Sheets("BENE").Select
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT A" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT A").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT A").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'CAT B
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT B" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT B").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT B").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'BENE CAT C
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT C" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT C").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT C").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'BENE CAT D
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT D" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT D").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT D").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'BENE CAT E
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT E" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT E").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT E").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'BENE CAT F
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT F" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT F").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT F").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Sheets("BENE").Select
    'BENE CAT G
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT G" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT G").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT G").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
            
    Sheets("BENE").Select
    'BENE CAT H
    [BO2].Select
    Do Until ActiveCell = ""
    If ActiveCell = "CAT H" Then
    ActiveCell.EntireRow.Copy
    Sheets("BENE CAT H").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("BENE").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("BENE CAT H").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
      Cells.Select
        Range("AL1").Activate
        Application.CutCopyMode = False
        Selection.Sort Key1:=Range("AU2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
      Sheets("control").Select
     End Sub
    Last edited by E3iron; 02-10-2010 at 10:12 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1