Results 1 to 6 of 6

Thread: [RESOLVED] (Excel 2010) - Macro freezes then Excel crashes

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2012
    Posts
    24

    Resolved [RESOLVED] (Excel 2010) - Macro freezes then Excel crashes

    First of all let me say that I am a self-taught Macro user by recording macros, googling, and reading VBA for dummies so I'm sure that I am brutalizing some parts of the macro.
    So here's what I'm trying to do. I have a list of clients on an "Intranet" that I use all the time for information. This client list changes with new clients coming in and old ones leaving so the bottom line is that it will be a variable. What I would like to do is type out a list of these clients and then have the macro go to the first cell, open the url and then paste it into a sheet that I named based on that cell as well. As an example, Say I have a client Jimmy. I put that name in Sheet1 cell A2. The macro goes to the cell and opens up the url. It then creates a Sheet and names it Jimmy and then pastes the info into that sheet. From there it loops to the next cell and the next until all the clients have their own sheet and the content of the url is pasted into the cell.

    I thought this was fairly easy to do and the code below works sometimes. Keep in mind that I have about 500 sheets that this macro will run through at a time. One time it will loop through about 50 clients and freeze, the next time it will loop through 250 and then freeze. PLEASE HELP ME!!! I suspect that there might be code that I can clean up a little or a line that I could change that would stop it from freezing.

    Hopefully I explained that well enough to understand!

    Thanks in advance.

    Code:
    Sub Update_Client_Tabs()
     Sheets("Start Tab").Select
    Range("F6").Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    sourcesheet = ActiveSheet.Name
    For Each Cell In Selection
        Sheets.Add After:=Sheets(Sheets.Count)
        
        ActiveSheet.Name = Cell.Value
        Range("A1").FormulaR1C1 = _
            "=MID(CELL(""filename"",RC),FIND(""]"",CELL(""filename"",RC))+1,256)"
        Range("B1").FormulaR1C1 = _
            "=INDEX('Start Tab'!C6:C9,MATCH(R1C1,'Start Tab'!C6,0),4)"
        Range("B1").Copy
        Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Range("A2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",R1C1)"
        Range("A2").Select
        Selection.AutoFill Destination:=Range("A2:A1000"), Type:=xlFillDefault
        Range("A2:A1000").Select
        
        Selection.FillDown
        
    ' This section goes to the website (Fact Sheet) and copies it to Excel
    
    Dim qtsQueries As QueryTables
    Dim qtQuery As QueryTable
    Set qtsQueries = ActiveSheet.QueryTables
    With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;" & Range("B2").Value, _
          Destination:=ActiveSheet.Range("b4"))
          
            .Name = Range("B2").Value
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
            
            
        End With
      Range("$A$2:$A$1000").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
        On Error Resume Next
        
        Range("B4").Select
        ActiveSheet.Buttons.Add(800, 5, 45, 25).Select
        Selection.OnAction = "Back"
        ActiveSheet.Shapes("Button 1").Select
        Selection.Characters.Text = "Back"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = _
            "=MID(CELL(""filename"",RC[-2]),FIND(""]"",CELL(""Filename"",RC[-2]))+1,256)"
       Worksheets(Worksheets.Count).Activate
       Sheets(sourcesheet).Activate
    
    
    Next Cell
    
    End Sub
    Last edited by Siddharth Rout; Jan 1st, 2013 at 10:16 AM. Reason: Added Code tags

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: (Excel 2010) - Macro freezes then Excel crashes

    first remove on error resume next, so that it can break on any errors

    avoid use of select, activate and selection, in most case these are not required, you should always work with fully qualified ranges
    Code:
    set startsht = Sheets("Start Tab")
    then work with startsht

    in the past it was possible to break never ending loops with ctrl break, but on todays machines, they are too fast and the break keys are not processed while the code is running, so all loops should contain doevents at some point to allow the break keys to be processed, it is not necessary to have doevents for every iteration of a loop, but safer than not having at all and having to crash excel
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2012
    Posts
    24

    Re: (Excel 2010) - Macro freezes then Excel crashes

    Holy Crap!!!!!! The DoEvents worked! I opened up 480 tabs and copied the corresponding url into the tab without it crashing! One question though. You said not to use select activate or selection. If I have the below code, how would you change it using "startsht"?

    Sheets("Start Tab").range("A1").select

    or

    Worksheets(Worksheets.Count).Activate

    Again thanks for all your help! You saved me hours of work already!!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: (Excel 2010) - Macro freezes then Excel crashes

    when working with multiple sheets, every range should be fully qualified,( that is specify the sheet and book)
    the best method is to set the sheets to be used to sheet object variables

    If I have the below code, how would you change it using "startsht"?
    Code:
    set soucesht = thisworkbook.sheets("sheet1")
    with sourcesht 
        .range("a1") = "xxx"
    end with
    set destsht = workbooks("someotherbook.xls").sheets("results")
    destsht.range("a1") = sourcesht.cells(1, 1)
    just about the only time you need to select is if you want to present something specific to the user on finishing
    using activesheet can allow for many errors to creep in especially if the code runs in background and the user clicks something
    Last edited by westconn1; Dec 21st, 2012 at 02:30 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2012
    Posts
    24

    Re: (Excel 2010) - Macro freezes then Excel crashes

    OK, that fixed my problem and saved me a boat load of time. Thank you for your assistance. Have a great holiday!

  6. #6
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: (Excel 2010) - Macro freezes then Excel crashes

    stephan2531, we are glad that your query is resolved.

    You might want to help us by marking the thread as resolved. This would let other experts know that your query is solved and they can skip the post if they wish to. This would help them concentrate on UNRESOLVED threads.

    If you have JavaScript enabled you can do that by selecting the Mark Thread Resolved item from the Thread Tools menu. Otherwise please insert [Resolved] at the start of the Subject and select the green check mark from the post icons.

    If someone has been particularly helpful you also have the ability(depending on your points) to affect their forum reputation by rating their post. More information about rating can be found here.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Posting Permissions

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



Click Here to Expand Forum to Full Width