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 that was built to group items with a + symbol in front. It works great except that it is always looking for the original workbook it was created in. I opened the original workbook and copied then paste in another workbook. If I move the original workbook the macro fails in the file I pasted the macro. It probably is something relatively easy that I am missing.

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

Any help with both these problems will greatly be appreciated. Here is 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.

Steve

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

--
regards,
Tom Ogilvy


"Toolman045" wrote:

>
> Hello: This has two (2) questions:
>
> The first and annoying question is: I have the following macro that
> was built to group items with a + symbol in front. It works great
> except that it is always looking for the original workbook it was
> created in. I opened the original workbook and copied then paste in
> another workbook. If I move the original workbook the macro fails in
> the file I pasted the macro. It probably is something relatively easy
> that I am missing.
>
> 2nd: I added a macro that will collapse the groups after my worksheet
> is updated. It does not recognize the coding on the bottom.
>
> Any help with both these problems will greatly be appreciated. Here is
> 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.
>
> Steve
>
>
> --
> Toolman045
> ------------------------------------------------------------------------
> Toolman045's Profile: http://www.excelforum.com/member.php...o&userid=35324
> View this thread: http://www.excelforum.com/showthread...hreadid=550986
>
>

[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?

--
Don Guillett
SalesAid Software
[email protected]
"Toolman045" <[email protected]> wrote
in message news:[email protected]...
>
> Hello: This has two (2) questions:
>
> The first and annoying question is: I have the following macro that
> was built to group items with a + symbol in front. It works great
> except that it is always looking for the original workbook it was
> created in. I opened the original workbook and copied then paste in
> another workbook. If I move the original workbook the macro fails in
> the file I pasted the macro. It probably is something relatively easy
> that I am missing.
>
> 2nd: I added a macro that will collapse the groups after my worksheet
> is updated. It does not recognize the coding on the bottom.
>
> Any help with both these problems will greatly be appreciated. Here is
> 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.
>
> Steve
>
>
> --
> Toolman045
> ------------------------------------------------------------------------
> Toolman045's Profile:
> http://www.excelforum.com/member.php...o&userid=35324
> View this thread: http://www.excelforum.com/showthread...hreadid=550986
>

[Toolman045]

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

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

I should tell you that someone else helped me create the code based on 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?

--
Regards,
Tom Ogilvy



"Toolman045" wrote:

>
> Don & Tom: Both of you provided great feedback. However, I know I have
> not assigned a button to the code and where would I search for the
> control?
>
> I am totally lost when you indicate to place the Macro in my personal
> xls so the code is avaiable everywhere.
>
> I should tell you that someone else helped me create the code based on
> what I needed to be accomplished in the workbook.
>
> Any more help will be appreciated.
>
>
> --
> Toolman045
> ------------------------------------------------------------------------
> Toolman045's Profile: http://www.excelforum.com/member.php...o&userid=35324
> View this thread: http://www.excelforum.com/showthread...hreadid=550986
>
>

[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).


--
Regards,
Tom Ogilvy

"Toolman045" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> Toolman045
> ------------------------------------------------------------------------
> Toolman045's Profile:
http://www.excelforum.com/member.php...o&userid=35324
> View this thread: http://www.excelforum.com/showthread...hreadid=550986
>