Results 1 to 6 of 6

Alternative to Loop

Threaded View

E3iron Alternative to Loop 10-02-2009, 05:52 AM
royUK Re: Country loop 10-02-2009, 06:00 AM
E3iron Re: Country loop 10-02-2009, 06:25 AM
royUK Re: Country loop 10-02-2009, 06:29 AM
E3iron Alternative to Loop 10-02-2009, 06:42 AM
DonkeyOte Re: Alternative to Loop 10-02-2009, 07:00 AM
  1. #1
    Registered User
    Join Date
    07-20-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    63

    Red face Alternative to Loop

    Hi

    I have the following code but it takes an age to run, can anyone help simplifiy it. The code looks for something in AC finds it and pastes it into another sheet.

    I have a spreadsheet which has 7 regions i need the macro to move each region to a certain sheet i.e RDA UK to UK etc.

    Thanks in advance

    Sub CountryLoop()
    'Uk
    Sheets("Combined").Select
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA UK" Then
    ActiveCell.EntireRow.Copy
    Sheets("UK").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("UK").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'Benelux
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA Benelux" Then
    ActiveCell.EntireRow.Copy
    Sheets("Bene").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("Bene").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'iberia
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA Iberia" Then
    ActiveCell.EntireRow.Copy
    Sheets("IBE").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("IBE").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'Germany
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA Germany" Then
    ActiveCell.EntireRow.Copy
    Sheets("GER").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("GER").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'France
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA France" Then
    ActiveCell.EntireRow.Copy
    Sheets("FRA").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("FRA").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'Finland
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA Finland" Then
    ActiveCell.EntireRow.Copy
    Sheets("FIN").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("FIN").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'Romania
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA Romaina" Then
    ActiveCell.EntireRow.Copy
    Sheets("CE").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("CE").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    Sheets("Combined").Select
    'CE Region
    [AC1].Select
    Do Until ActiveCell = ""
    If ActiveCell = "RDA CE Region" Then
    ActiveCell.EntireRow.Copy
    Sheets("CE").Select
    [A65536].Select
    Selection.End(xlUp).Select
    If ActiveCell <> "" Then ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Paste
    Sheets("Combined").Select
    Else
        End If
    ActiveCell.Offset(1, 0).Select
    Loop
    [a1].Select
    
    Sheets("CE").Select
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit
    ActiveCell.Select
    
    
    
    '
    End Sub
    Last edited by E3iron; 10-02-2009 at 06:44 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