Macro Opening or Looking for Another Workbook

[Toolman045]

Hello: This has two (2) questions:

The first and annoying question is: I have the following macro tha
was built to group items with a + symbol in front. It works grea
except that it is always looking for the original workbook it wa
created in. I opened the original workbook and copied then paste i
another workbook. If I move the original workbook the macro fails i
the file I pasted the macro. It probably is something relatively eas
that I am missing.

2nd: I added a macro that will collapse the groups after my workshee
is updated. It does not recognize the coding on the bottom.

Any help with both these problems will greatly be appreciated. Here i
my code:


Sub GroupData()

Dim wb As Workbook, ws As Worksheet
Dim cel As Range, GroupStart As Range, FirstCel As Boolean
Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
If ws.Name = "52_Weeks" Or ws.Name = "13_Weeks" Or ws.Name = "YTD
Then
ws.Activate
ws.Cells.ClearOutline
For Each cel In ws.Range("A1", ws.Range("A65535").End(xlUp).Offset(1
0))
If Left(cel, 1) = "+" And FirstCel = False Then
Set GroupStart = cel
FirstCel = True
End If
If Left(cel, 1) <> "+" And FirstCel = True Then
ws.Range(GroupStart, cel.Offset(-1, 0)).Select
Selection.Rows.Group
FirstCel = False
End If
If cel.Address = ws.Range("A65535").End(xlUp).Offset(1, 0) Then
ws.Range(GroupStart, cel.Offset(-1, 0)).Select
Selection.Rows.Group
FirstCel = False
End If


Next cel
End If
Next ws

Sheets("YTD").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("A1").Select
Sheets("52_Weeks").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("A1").Select
Sheets("13_Weeks").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("A1").Select

End Sub



Again, thanks for your help.

Stev

 

[Tom Ogilvy]

Based on your explanation, I would suspect you have the macro assigned to a
button or other control. Go into that control and reasign the macro or
change the location of the macro it is looking for.

 

[Don Guillett]

maybe you have assigned to a button with a link

edit>links>change links

maybe you want to put in your personal.xls to be available everywhere?

 

[Toolman045]

Don & Tom: Both of you provided great feedback. However, I know I hav
not assigned a button to the code and where would I search for th
control?

I am totally lost when you indicate to place the Macro in my persona
xls so the code is avaiable everywhere.

I should tell you that someone else helped me create the code based o
what I needed to be accomplished in the workbook.

Any more help will be appreciated

 

[Tom Ogilvy]

there is nothing in your code to cause it to refer to the original workbook.
Set wb = ActiveWorkbook

is used at the top of the code and your references seem to build down from
that - so if the original workbook is not open, it should not be invovled.

How do you run this macro?

 

[Toolman045]

Tom Wrote:

there is nothing in your code to cause it to refer to the original
workbook.
Set wb = ActiveWorkbook

is used at the top of the code and your references seem to build down
from
that - so if the original workbook is not open, it should not be
invovled.

How do you run this macro?

--
Regards,
Tom Ogilvy

Tom: I agree with you. However, if I do not have the original
workbook called Master Hierachy Report in the same directory of the
workbook I am working on, the macro says "Can't Find Master Hierarchy
Report" If it is in the same directory, the Macro opens the master
hierachhy Report to run the macro in the workbook I am working on.

I have an Add-in program in my Excel named XLerate. The add-in alllows
me to build ranges for Time, Product, geography and measures in a range.
After building the range, I have a tool in my macro that allows me to
run a macro after the update. All I have to do is point to the macro
that is in the current workbook I am working on (GroupData).

Now that I am writing this. The Groupdata macro name is the sub name
that is in the Master Heirachy workbook. Do you think it is open the
same macro with the same names in the same folder?

Tom thanks again for any help. Do we know why the macro does not close
or collaspe the groups?

Steve

 

[Tom Ogilvy]

I don't know anything about your addin, but it sounds like it has been set
up to run the macro from your old workbook (it contains the full path and so
forth to your old workbook). If you want to run a different macro, you
will need to figure out how to modify the setting in the addin (my guess).