Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
I've developed a large workbook with a macro that creates multiple copies of three template sheets and fills them in using data from various other hidden sheets, then extracts 15 datasheets from a source pivot table, then moves all these new sheets into a new workbook, which is then saved as a report, leaving the original workbook in its original state. The macro was working, with a few possible bugs, but now it's suffering complete failure. It successfully moves most of the sheets but then after moving seven of the datasheets, the macro fails (no chance to debug) halfway through moving the data sheets. Bizarrely, I then cannot manually move them (no error, Excel just does nothing with it) so I'm sure it's not a coding error. I could still copy the sheet, so I changed the macro to do this and delete the original, but it still fails, oddly enough at the same place. Any ideas why this is happening? Or even any clues as to what I should be looking for? I'm quite new to VB so I may have missed something fundamental in the way I've set this up. For example, I'm setting WB publicly as the active workbook at the beginning of the macro, and referring to it throughout, but I've no idea if I should be doing this as I'm effectively increasing the memory requirements of WB as I go along. This is certainly my first instinctive thought on it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
I appreciate that Don, but as I am certain the code itself is not to blame, I
don't see the sense in posting 9 modules and a few thousand lines! I do suspect that setting WB = "REPORT.xls" at the start and adding sheets to this WB as I go along could be the root of my problem, but I am new to coding so I don't know if I'mbarking up the wrong tree. The entire report size is approx 3.5MB and the source "report.xls" is approx 80MB using external data sources. I've created a monster. Regards Tom "Don Guillett" wrote: Always post your code for comments and suggestions. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
It does sound as if you have created a MONSTER. Excel does NOT like large external data bases. It sounds like you need professional help to re-design your project. I'm pretty sure it could be greatly simplified. -- Don Guillett Microsoft MVP Excel SalesAid Software "baldmosher" wrote in message ... I appreciate that Don, but as I am certain the code itself is not to blame, I don't see the sense in posting 9 modules and a few thousand lines! I do suspect that setting WB = "REPORT.xls" at the start and adding sheets to this WB as I go along could be the root of my problem, but I am new to coding so I don't know if I'mbarking up the wrong tree. The entire report size is approx 3.5MB and the source "report.xls" is approx 80MB using external data sources. I've created a monster. Regards Tom "Don Guillett" wrote: Always post your code for comments and suggestions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
This is the macro that's failing: Sub save_report() '1. select five new 'mandatory' (O/G/E/I/R) sheets from report master file and dump into new WB ' -- uses selname and resname set above to select O and R sheets '2. dump customer & drill sheets into new WB '3. add glossary(ies) '4. save & close new WB Dim M_S As Object Set M_S = MATRIX_STATIONS 'sheet used for misc referencing Dim fn As String Dim WSn(0, 4) As String Dim sh As Worksheet Dim blnReplace As Boolean 'this isn't used now as I process one sheet at a time Dim w As Integer Dim stn As String Dim WSo As Object Dim src As String, bkmk As String, txt As String Set WB = Workbooks("REPORT.xls") 'testing ' Set M_M = MATRIX_MISC 'similar to M_S set above ' selname = "Overview MAN 2009-06" ' BR = "BMAN" ' resname = "MAN Alerts 2009-06" '-------------------- ' MANDATORY SHEETS '-------------------- 'select mandatory (O/G/E/I/A) sheets from scorecard master file and dump into new WB WSn(0, 0) = selname WSn(0, 1) = "General" WSn(0, 2) = "Export" WSn(0, 3) = "Import" WSn(0, 4) = resname 'only works on sheets that exist If fn_SheetExists(WSn(0, 0)) = False Then MsgBox "WHOOPS! No overview sheet = error" GoTo Skip Else: With Sheets(selname) If RptLvl = "R" Then .Shapes("ShowCustomerDataButton").Delete .Shapes("GoToAlertsButton").Delete End If If RptLvl = "U" Then .Shapes("GoToAlertsButton").Delete End If End With For w = 1 To 3 'G/E/I 'only if G/E/I sheet exists If fn_SheetExists(WSn(0, w)) = True Then 'add sheet hyperlinks as appropriate Set WSo = Sheets(WSn(0, w)) With WSo.Range("A1") 'add Overview link If fn_SheetExists(WSn(0, 0)) = True Then With .Offset(0, 12) src = "A1" bkmk = "'" & selname & "'!A1" txt = "Overview" .Hyperlinks.Add _ Anchor:=.Range(src), _ Address:="", _ SubAddress:=bkmk, _ TextToDisplay:=txt .Font.Size = 8 End With Else: .Offset(0, 12).Value = " " End If 'add General link If fn_SheetExists(WSn(0, 1)) = True Then If Not WSo.Name = "General" Then With .Offset(0, 13) src = "A1" bkmk = "'General'!A7" txt = "General" .Hyperlinks.Add _ Anchor:=.Range(src), _ Address:="", _ SubAddress:=bkmk, _ TextToDisplay:=txt .Font.Size = 8 End With End If Else: .Offset(0, 13).Value = " " End If 'add Export link If fn_SheetExists(WSn(0, 2)) = True Then If Not WSo.Name = "Export" Then With .Offset(0, 14) src = "A1" bkmk = "'Export'!A7" txt = "Export" .Hyperlinks.Add _ Anchor:=.Range(src), _ Address:="", _ SubAddress:=bkmk, _ TextToDisplay:=txt .Font.Size = 8 End With End If Else: .Offset(0, 14).Value = " " End If 'add Import link If fn_SheetExists(WSn(0, 3)) = True Then If Not WSo.Name = "Import" Then With .Offset(0, 15) src = "A1" bkmk = "'Import'!A7" txt = "Import" .Hyperlinks.Add _ Anchor:=.Range(src), _ Address:="", _ SubAddress:=bkmk, _ TextToDisplay:=txt .Font.Size = 8 End With End If Else: .Offset(0, 15).Value = " " End If 'add Alerts link If fn_SheetExists(WSn(0, 4)) = True Then With .Offset(0, 16) src = "A1" bkmk = "'" & resname & "'!A1" txt = "Alerts" .Hyperlinks.Add _ Anchor:=.Range(src), _ Address:="", _ SubAddress:=bkmk, _ TextToDisplay:=txt .Font.Size = 8 End With Else: .Offset(0, 16).Value = " " End If End With End If Next w 'select sheets blnReplace = True 'first sheet replaces selected For w = 0 To 4 If fn_SheetExists(WSn(0, w)) = True Then Sheets(WSn(0, w)).Select blnReplace blnReplace = False 'subsequent sheets add to selected array Next w End If 'protect and move/copy to create new WB ActiveWindow.SelectedSheets.Move 'remember new WB name fn = ActiveWorkbook.Name '--------------- ' DATA SHEETS '--------------- If Not RptLvl = "B" Then GoTo SkipData '!! is this required? WB.Activate stn = " Data " 'commented, owing to sheet move errors ' blnReplace = True ' For Each sh In WB.Worksheets ' If InStr(1, sh.Name, stn) Then ' sh.Select blnReplace ' blnReplace = False ' End If ' Next sh ' ''move to new WB ' ActiveWindow.SelectedSheets.Copy After:=Workbooks(fn).Sheets(Workbooks(fn).Sheets.C ount) ' WB.Activate ' ActiveWindow.SelectedSheets.Delete For Each sh In WB.Worksheets If InStr(1, sh.Name, stn) Then sh.Copy After:=Workbooks(fn).Sheets(Workbooks(fn).Sheets.C ount) sh.Delete Else: End If Next sh SkipData: '!!!!!! '!!!!!! 'macro fails during sheets.move in the next part '!!!!!! '!!!!!! '------------------- ' CUSTOMER SHEETS '------------------- If RptLvl = "R" Then GoTo SkipCustomer '!! is this required? WB.Activate 'source workbook 'move any customer & drill output sheets (all contain the station/region name or "UK") stn = "Cust" For Each sh In WB.Worksheets If InStr(1, sh.Name, stn) Then sh.move After:=Workbooks(fn).Sheets(Workbooks(fn).Sheets.C ount) Next sh SkipCustomer: '----------------- ' GLOSSARY(IES) '----------------- Workbooks(fn).Sheets.Add After:=Sheets(Workbooks(fn).Worksheets.Count) With ActiveSheet .Name = "Glossary" .Tab.ColorIndex = 1 M_M.Range("__Glossary").Copy .PasteSpecial .Columns("A:I").EntireColumn.AutoFit .Columns("A").EntireColumn.Hidden = True .Columns("C").EntireColumn.Hidden = True .Columns("F:G").EntireColumn.Hidden = True showallobjects 'prevents errors in hiding columns and rows .Range(Range("IV1"), Range("IV1").End(xlToLeft).Offset(0, 1)).EntireColumn.Hidden = True .Range(Range("A65536"), Range("A65536").End(xlUp).Offset(0, 1)).EntireRow.Hidden = True .Range("B1").Value = "Description" 'removes the words "max width 245p" .Range("A1").Select End With '------------------- ' SAVE NEW REPORT '------------------- 'all sheets are now in new workbook "fn" Workbooks(fn).Activate With ActiveWorkbook 'add timestamp to overview sheet .Sheets(selname).Range("TO_datestamp") = "Produced " & Now 'finalise all sheets in new report Do Until w = .Sheets.Count With .Sheets(w) 'cancel Build in Progress If Range("A1") = "BIP" Then Range("A1") = "" 'hide customer data sheets If InStr(1, .Name, "Cust") Then .Visible = False 'password protect with PW .Protect Password:=PW, DrawingObjects:=True, Contents:=True, Scenarios:=True, _ AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _ AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, _ AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True 'set focus on all sheets to A1 '.Range("A1").Select End With w = w + 1 Loop 'hide sheet tabs ActiveWindow.TabRatio = 0 'save new report to (shared drive) 'path has generic format "\\UKOV\RGN\BSTN\" If RptLvl = "B" Then 'set new fn before save: "STN YYYY-MM" (len 11) fn = Right(selname, 11) & ".xls" 'set path (testing will save to My Docs, may eventually need to swap for production lookup from M_R) Dim path As String path = M_M.Range("_PATH_reports") _ & WorksheetFunction.Index(M_S.Range("StnRReport"), WorksheetFunction.Match(BR, M_S.Range("StnBSTN"), 0)) & "\" _ & BR & "\" ElseIf RptLvl = "R" Then fn = Right(selname, 12) & ".xls" path = M_M.Range("_PATH_reports") _ & BR & "\" ElseIf BR = "UKOV" Then fn = Right(selname, 12) & ".xls" path = M_M.Range("_PATH_reports") End If .Sheets(1).Select .SaveAs Filename:=path & fn .Close End With SkipSave: Skip: End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
Don, I will be in touch for your testimony when I approach the IT department
for more funding and an assistant. ;-) Seriously though, it's certainly a leaky memory problem because on the advice of a colleague I restarted Windows (why didn't I think of that) and it worked fine. Not sure how long it'll run for before it fails again (it is supposed to create 23 reports one after the other) but I guess I should firstly get out of the habit of hitting standby when I finish for the day. And you're almost certainly right that it can be simplified. I've been learning VB as I go along for about 2 years, mostly using this forum and Google for help, so I have no doubt there are better ways of doing this! Thanks and kind regards Tom "Don Guillett" wrote: It does sound as if you have created a MONSTER. Excel does NOT like large external data bases. It sounds like you need professional help to re-design your project. I'm pretty sure it could be greatly simplified. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
Baldmosher,
In your code as posted : Set WB = Workbooks("Report.xls") But where is: Dim WB As Workbook Also l seem to remember some years ago using XL2003 that the number of sheets deleted / created using VBA was restricted to a certain number that depended on their individual size & complexity. I am sure that there will be something posted on this forum about the subject. What version of XL are you using? Regards Michael |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets won't move
Michael, WB is set Public, and I'm using XL03 so I will look into that. Thanks for the pointer! Tom "michael.beckinsale" wrote: Baldmosher, In your code as posted : Set WB = Workbooks("Report.xls") But where is: Dim WB As Workbook Also l seem to remember some years ago using XL2003 that the number of sheets deleted / created using VBA was restricted to a certain number that depended on their individual size & complexity. I am sure that there will be something posted on this forum about the subject. What version of XL are you using? Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move through the sheets of a workbook | Excel Programming | |||
Cannot Move or Copy Sheets | Excel Discussion (Misc queries) | |||
Use Tab key to move between tab sheets | Excel Worksheet Functions | |||
sequential move across sheets | Excel Programming | |||
Move data to different sheets | Excel Programming |