path to macros being changed

R

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
 
S

Simon Lloyd

Risky said:
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
Click to expand...
Dave can you supply the workbook?, it may be that you have copied the
workbook and are using that in TOOLS>MACRO>MACROS you see a textbox
entitled "Macro's in" if this says All open workbooks or This Workbook
then that may be the possible cause, it should show e.g example,xls


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
R

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("a5:eek:505").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
 
O

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.
 
S

Simon Lloyd

Dave, for each of your macro's make sure it is set to the actual name of
the workbook in the dropdown, the problem is because you have it set to
this workbook, when creating a new workbook (thats copying the extracted
worksheet) it becomes the "This Workbook", this workbook also means the
active workbook.
Risky said:
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!):
Code:
--------------------
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("a5:eek:505").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"

' transpos 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


Office Discussion' (http://www.thecodecage.com))
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=102015)
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
R

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
 
R

Risky Dave

Simon,

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

Dave
 
R

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
 
O

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Turning off automatic calculation 0
Need Help with Macro Security 1
Macros Being Re-Directed 0
Save workbook without shapes 0
Dumb question alert! What program to use? 0
Problem in adding macro to ribbon through Add-in 7
Add and Delete sheets 3
adding footer to all existing sheets in workbook and also to newsheets which is added 1

Top