+ Reply to Thread
Results 1 to 4 of 4

Slowness issue with macro code (adding sheets in excel 2013)

Hybrid View

  1. #1
    Registered User
    Join Date
    07-27-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    25

    Exclamation Slowness issue with macro code (adding sheets in excel 2013)

    Hi, I am trying to run a macro in excel 2013 but it is very slow. It runs way faster (30 secs) on my PC with Excel 2007 but takes around 10 mins to run on my dell latitude i5 8GB ram laptop. Everything works fine except a code which is adding sheets. Excel 2013 hangs while adding sheets in a loop (For lp = 1 To r2 Step 1
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Worksheets("Opportunity Owners").Cells(lp, 1).Text

    Application.Wait (Now + TimeValue("0:00:05"))

    Next lp)
    . Please let me what are the possible solutions. Thanks in advance!

    Sub Sep_sheets()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim r As Integer, x As String, r2 As Integer, lp As Integer, lp2 As Integer, lp3 As Integer
    
        Range("A1").Select
        Selection.EntireColumn.Insert
        Columns("W:W").Select
        Selection.Copy
        Application.CutCopyMode = False
        Selection.Cut
        Range("A1").Select
        ActiveSheet.Paste
        Columns("O:U").Select
        Selection.EntireColumn.Hidden = True
        Range("V1").Select
        Selection.End(xlToLeft).Select
    
    
    'Application.Calculation = xlCalculationManual
    
    
    Range("A1").Select
        Application.Goto Reference:="R100000C1"
        Selection.End(xlUp).Select
       r = ActiveCell.Row
    
    x = ActiveSheet.Name
    
    Rows(r + 2).Select
    Selection.Delete Shift:=xlUp
    Rows(r + 2).Select
    Selection.Delete Shift:=xlUp
    Rows(r + 2).Select
    Selection.Delete Shift:=xlUp
    Rows(r + 2).Select
    Selection.Delete Shift:=xlUp
    Rows(r + 2).Select
    Selection.Delete Shift:=xlUp
    
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Opportunity Owners"
    
    Sheets(x).Activate
    Range(Cells(2, 1), Cells(r, 1)).Select
    Selection.Copy
    Sheets("Opportunity Owners").Activate
     Range("A1").Select
        ActiveSheet.Paste
    
        Dim data As Variant, temp As Variant
        Dim obj As Object
        Dim i As Long
        Set obj = CreateObject("scripting.dictionary")
        data = Selection
        For i = 1 To UBound(data)
            obj(data(i, 1) & "") = ""
        Next
        temp = obj.keys
        Selection.ClearContents
        Selection(1, 1).Resize(obj.Count, 1) = Application.Transpose(temp)
    
    
    Range("A1").Select
        Application.Goto Reference:="R100000C1"
        Selection.End(xlUp).Select
       r2 = ActiveCell.Row
    
    For lp = 1 To r2 Step 1
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Worksheets("Opportunity Owners").Cells(lp, 1).Text
    
    Application.Wait (Now + TimeValue("0:00:05"))
    
    Next lp
    
    For lp2 = 3 To r2 + 2
    Sheets(x).Activate
     'Sheets("DBD --Rachel Ribbeck").Activate
      Cells.Select
        Selection.Copy
        Sheets(lp2).Select
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste
    Next lp2
    
    
    For lp2 = 3 To r2 + 2
    Sheets(lp2).Activate
    
    For lp3 = 2 To r
    If Cells(lp3, 1) <> ActiveSheet.Name And Cells(lp3, 1) <> "" Then
    'Range(Cells(lp3, 1), Cells(lp3, 1)).Activate
     Rows(lp3).Select
        Selection.Delete Shift:=xlUp
    lp3 = lp3 - 1
    End If
    Next lp3
    Range("a1").Select
    Next lp2
    'Application.Calculation = xlCalculationAutomatic
    
    End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Slowness issue with macro code (adding sheets in excel 2013)

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    07-27-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    25

    Re: Slowness issue with macro code (adding sheets in excel 2013)

    Hi, Sorry for the late response.
    Lets forget rest of the code for a while. I have found out that adding sheets in excel in general is causing problems. If I try to add sheets one by one, after 10 or 15 sheets it starts causing slowness. i was previously working with Excel 2010 but never had such issue. This is only with the 2013 version. What can bet the cause of the issue and how do I resolve it?

    Thanks,
    Vibhor

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Slowness issue with macro code (adding sheets in excel 2013)

    I'm using 2010 so I guess I can't help you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issue adding PowerPivot Add-In Excel 2013
    By Shearwater in forum Excel General
    Replies: 1
    Last Post: 06-02-2016, 05:58 AM
  2. Excel 2013 issue
    By gill123 in forum Excel General
    Replies: 1
    Last Post: 02-24-2016, 11:36 AM
  3. Macro code working fine in excel 2013 but not in excel 2010.
    By sere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-10-2014, 02:23 AM
  4. Replies: 3
    Last Post: 07-20-2014, 11:46 AM
  5. VBA Create Sheets Excel 2013 Issue
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2014, 10:00 AM
  6. Replies: 0
    Last Post: 10-21-2013, 02:14 PM
  7. Macro issue may have when upgrade excel from 2003 to 2013
    By lhollis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2013, 03:21 AM

Tags for this Thread

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