path to macros being changed

[Risky Dave]

Hi,

I have a (quite complex) workbook with a considerable amount of code behind
it.

One macro copies the content of various sheets in the workbook to a new
temporary sheet moves that sheet to a new workbook, renames the new workbook
and deletes the temporary sheet in the source workbook.

When this macro (called "Extract") runs, the path to all other macros in the
source workbook are being pointed at the newly created workbook meaning that
I need to go into the Macros properties in XL and reset tham back to the
original workbook. Given that there is some 40-odd macros in the book, this
is completely impractical (it's also impossible for me to ask my users to do
this every time they run Extract).

Having played with the code, the following are the lines that are
re-directing the macros:

' copy extract to a new sheet

Sheets("Extract").Select ' this is the temp sheet in the source workbook
Application.CutCopyMode = False
Sheets("Extract").Move

Can someone please explain why this has started to happen (I have been using
this code for a couple of months without any problems - it has only started
to happen in the last week) and - more importantly - how do I fix it?

TIA

Dave

 

[Risky Dave]

Simon,

Thanks for the quick response.

This is part of what is being changed! I am setting the macro path to "This
Workbook" but when Extract is run this is chnaged to "All open Workbooks".

The workbook is prretty big (about 800k with no data in it), but here's the
full code for Extract (I don't claim to be a programmer, so I'm sure that it
could be improved in all sorts of ways - sughestions always welcome!):

Sub Risk_extract()

Dim rNumberCount As Range ' used to track risk numbers
Dim rHistoryCell As Range ' used to track history entries per risk
Dim sStorage As String ' used to store concatenated entries
Dim rExtractCell As Range ' used to place history data on the extract sheet
Dim rCostDataS As Range ' used to transpose cost data in "Extract"
(source)
Dim rCostDataT As Range ' used to transpose cost data into "Extract"
(target)
Dim iNumberOfRows As Integer ' count number of costing rows to transpose
Dim lNumberOfColumns As Long ' count number of costing risks to transpose
Dim sMyRegister As String ' used to capture the name of the current workbook
Dim sMyPath As String ' used to create the save path for the extract

Application.ScreenUpdating = False
sMyPath = Sheets("user data").Range("b4")

'copy basic risk information

Sheets("Identification").Range("a5505").copy

Sheets.Add.Name = "Extract"
With Sheets("extract")
.Range("A1").Select
.Paste
.Rows("1:1").Select
.Application.CutCopyMode = False
.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With

' concatenate history into column p

Set rNumberCount = Sheets("History storage").Range("a1")
Set rHistoryCell = rNumberCount.Offset(1, 0)
Set rExtractCell = Sheets("extract").Range("p4")
sStorage = ""

Do
If rNumberCount = "" Then
Exit Do
Else
Do
If rHistoryCell.Value <> "" Then
sStorage = sStorage & rHistoryCell.Value & Chr(10)
Set rHistoryCell = rHistoryCell.Offset(1, 0)
Else
rExtractCell.Value = sStorage
Exit Do
End If
Loop
Set rNumberCount = rNumberCount.Offset(0, 1)
Set rHistoryCell = rNumberCount.Offset(1, 0)
Set rExtractCell = rExtractCell.Offset(1, 0)
sStorage = ""
End If
Loop

' copy risk register pages to temporary storage called Extract

Sheets("assessment").Range("c5:r505").copy
Sheets("extract").Select
Range("p1").Select
ActiveSheet.Paste

Sheets("treatment - controls").Range("c5:r505").copy
Sheets("extract").Select
Range("af1").Select
ActiveSheet.Paste

Sheets("treatment - mitigations").Range("c5:w505").copy
Sheets("extract").Select
Range("av1").Select
ActiveSheet.Paste

Sheets("treatment - contingency").Range("c5:e505").copy
Sheets("extract").Select
Range("bq1").Select
ActiveSheet.Paste

' create costing data headings

Range("bt3").Value = "Mitigation 1 Cost"
Range("bu3").Value = "Mitigation 2 Cost"
Range("bv3").Value = "Mitigation 3 Cost"
Range("bw3").Value = "Mitigation 4 Cost"
Range("bx3").Value = "Mitigation 5 Cost"
Range("by3").Value = ""
Range("bz3").Value = ""
Range("ca3").Value = ""
Range("cb3").Value = ""
Range("cc3").Value = "Unmitigated Exposure"
Range("cd3").Value = "Cost To Mitigate"
Range("ce3").Value = "Mitigated Exposure"
Range("cf3").Value = "Recommendation"
Range("cg3").Value = "Threat/Opportunity"
Range("ch3").Value = "Cost of Risk"

' transpose cost data in "extract"

Set rCostDataS = Sheets("costings").Range("a3")
Set rCostDataT = Sheets("extract").Range("bt4")
iNumberOfRows = 16
lNumberOfColumns = rCostDataS.End(xlToRight).Column - rCostDataS.Column + 1

rCostDataS.Resize(iNumberOfRows, lNumberOfColumns).copy
rCostDataT.PasteSpecial Transpose:=True
Application.CutCopyMode = False

Range("by:cb,cg:cg").Delete

' top left justify all data cells

With Sheets("extract").Range("4:1000")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With

' copy extract to a new sheet

Sheets("Extract").Select
Application.CutCopyMode = False
Sheets("Extract").Move

' save new sheet with a specified name

Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sMyPath & Format(Date, "yymmdd") & " Risk & Issue
Register Extract.xls"
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Thanks

Dave

 

[OssieMac]

Hi Dave,

I think that anywhere you have lines like the following where you are
setting range variables in the Extract sheet (which you are moving to another
workbook) should be set to nothing before moving the sheet.

Following example in your code
Set rExtractCell = Sheets("extract").Range("p4")

Before moving the sheet apply the following code.

Set rExtractCell = Nothing

When you move the sheet, the range variable still refers to the moved sheet
at its new location.

 

[Risky Dave]

OssieMac,

Thanks for the response.

I have done as you suggested and unfortunately this has made no difference.

I have just tried the workbook on two other machines (one runnning Office
'07 under Vista Home and one running Office '03 under Vista Premium) and it
worked on both of them.

This suggests to me that it may be my local machine (Office '07 under Vista
Premiuim) that is causing the problem. Given that the problem is sudden and
recent - could it be the result of a patch/update (I keep the machine I'm
developing on as up to date as I can - the other machines may not be fully
patched)?

If this is the case, are there any suggestions on what may have chnaged?

TIA

Dave

 

[Risky Dave]

Simon,

Thanks. just tried this and no difference - the path is still being changed
to point at the newly created workbook.

Dave

 

[Robert McCurdy]

Without dissecting all your code Dave, here are a few suggestions.

1 Don't copy sheets. Copy the values either with pastespecial or
UsedRange or both.
2 Don't select if you can avoid it. Use a variable and set it to the
correct object.
3 Use one table or List per sheet.
4 Use arrays to hold worksheet values, process them in VBA, then return
them to the sheet.
5 Shift your main code to a xla type workbook (its called an Add-Inn).

Example of a simple array:
Save the headers as a named range, lets say we call it AxRay in this format
below.

={"Mitigation 1 Cost","Mitigation 2 Cost","Mitigation 3 Cost","Mitigation 4
Cost","Mitigation 5 Cost","","","","","Unmitigated Exposure","Cost To
Mitigate","Mitigated Exposure","Recommendation","Threat/Opportunity","Cost
of Risk"}

Then you can use it like this:
Range("Extract!bt3").Resize(1,15).Value = [AxRay]

It even works if the sheet Extract is not selected.


Regards
Robert McCurdy

 

[OssieMac]

Hi Dave,

Only other suggestion I have you have probably already done but here it is
anyway. Check all of your Excel options and Add-ins against the other
computer with office 2007. Also in the VBA editor check Tools -> References
and see if any differences.

I'll sign out of this now because I am going to be away for a few days.