Databases
StackOverflow2013
Postsdbo
POCan I make this macro more efficient or faster?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I make this macro more efficient or faster?
    primarykey
    data
    text
    <p>I am brand new to coding. This macro runs slow and I'm hopeful that someone can help me clean it up. Thanks in advance for your help. </p> <p>I developed the code to update my company's "Call Router" worksheet with new leads purchased from an outside source. The leads come to us in raw format in a worksheet called Fresh Agents Leads. Once the "Fresh Agent Leads" sheet is copied into the "MSS Call Routing Master List" file which houses the "Call Router" Worksheet, the macro reduces the raw data so that the parts we do not use are eliminated. It then re-formats what remains to match the formatting of the Old Call Router Worksheet and merges the two. It then renames the new Master sheet to Call Router. </p> <p>The code is designed to start inside the workbook that houses the Fresh Agent Leads Sheet. The user is instructed to have both the Fresh Agents Leads File and the MSS Call Routing Master List opened on the desktop before executing the code. </p> <pre><code> Sheets("Fresh Agent Leads").Select Sheets("Fresh Agent Leads").Copy After:=Workbooks( _ "MSS Call Routing Master List.xlsx").Sheets(1) Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select Selection.Copy Columns("F:F").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("E:E").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("G:S").Select Selection.Delete Shift:=xlToLeft Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ActiveCell.FormulaR1C1 = "=CONCATENATE(RIGHT(RC[1],4))" Range("C1").Select Selection.AutoFill Destination:=Range("C1:C1048575") Range("C1:C1048575").Select Sheets("Call Router").Select Rows("1:1").Select Selection.Copy Sheets("Fresh Agent Leads").Select Rows("1:1").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Application.Run "PERSONAL.xlsb!MergeIdenticalWorksheets" Columns("C:C").Select Selection.NumberFormat = "0000" Range("A:A,B:B,F:F").Select Range("F1").Activate Selection.ColumnWidth = 14 Columns("E:E").Select Selection.ColumnWidth = 25 Columns("C:C").Select Selection.ColumnWidth = 8.29 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rows("1:1").Select Selection.RowHeight = 30 With Selection .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With Columns("D:D").Select Selection.EntireColumn.Hidden = True Range("E2").Select ActiveWindow.FreezePanes = True Sheets(Array("Call Router", "Fresh Agent Leads")).Select Sheets("Call Router").Activate ActiveWindow.SelectedSheets.Delete Sheets("Master").Select Sheets("Master").Name = "Call Router" Range("C23").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWorkbook.Save End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpnuts
    1. USThomas
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCan I make this macro more efficient or faster?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POCan I make this macro more efficient or faster?
      1. USMakah
      1. VTFavorite
    1. COHave you tried looking at how expensive this line is? Selection.AutoFill Destination:=Range("C1:C1048575")
      singulars
      1. POCan I make this macro more efficient or faster?
      1. USJ Trana
    2. COi agree with J Trana. But most of your code needs refining. I didn't see your `Variable` declaration but i am guessing you declared none. Also you use `Select` too much that it is affecting the speed of your code. Try to adapt to what's discussed [here](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) regarding avoiding select and improving your code speed. if you came across things you cannot comprehend, update your question and paste your code, error and or dificulties encountered.
      singulars
      1. POCan I make this macro more efficient or faster?
      1. USL42
    3. COA more representative title would help a lot.
      singulars
      1. POCan I make this macro more efficient or faster?
      1. USsancho.s
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload