-
Dec 19th, 2012, 10:03 AM
#1
Thread Starter
Junior Member
[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
-
Dec 19th, 2012, 03:33 PM
#2
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
-
Dec 20th, 2012, 03:36 PM
#3
Thread Starter
Junior Member
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!!
-
Dec 21st, 2012, 02:24 AM
#4
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
-
Dec 21st, 2012, 08:54 AM
#5
Thread Starter
Junior Member
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!
-
Jan 1st, 2013, 10:15 AM
#6
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|