Macro "follows" a worksheet moved to a new workbook. How to stop this?

[RyanScharfy]

I'm in the process of converting to Excel 2007 from Excel 2003. I have buttons with assigned macros that manipulate data on other worksheets within the same workbook, then separate a worksheet into a new workbook.

With Excel 2007, my buttons with the assigned macros don't remain static - I can click the button once and the correct report generates. I go to click the button again, but it won't run because the macro assignment is somehow attached to the new workbook.

Any easy fix?

 

[SkipVought]

Hi,

Depends on how its coded. Need to see your relevant code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RyanScharfy]

Hi Skip,

Here's sample code:

Sub Macro7()
'
' Macro7 Macro
'

'
Sheets("Sheet2").Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A50") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:B50")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Copy After:=Sheets(3)
Range("A1:B1").Select
Selection.Font.Bold = True
Sheets("Sheet2 (2)").Select
Sheets("Sheet2 (2)").Move
End Sub

 

[PHV]

I'd use full qualified ranges:, eg

With ActiveWorkbook.Worksheets("Sheet2")
  .Select
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=.Range("A2:A50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Sort.SetRange .Range("A1:B50")
  .Sort.Header = xlYes
  .Sort.MatchCase = False
  .Sort.Orientation = xlTopToBottom
  .Sort.SortMethod = xlPinYin
  .Sort.Apply
  .Select
  .Copy After:=ActiveWorkbook.Sheets(3)
End With
ActiveWorkbook.ActiveSheet.Range("A1:B1").Font.Bold = True
With ActiveWorkbook.Sheets("Sheet2 (2)")
  .Select
  .Move
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

It is very important that EACH OBJECT have the correct reference when working with multiple workbooks and sheets. I had to GUESS at the sheet copy

Sub Macro7()
'
' Macro7 Macro
'
'
    With ThisWorkbook.Sheets("Sheet2")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=.Range("A2:A50"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .SetRange ThisWorkbook.Sheets("Sheet2").Range("A1:B50")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Copy After:=ThisWorkbook.Sheets(3)
        With ThisWorkbook.ActiveSheet
            .Range("A1:B1").Font.Bold = True
            .Move
        End With
    End With
End Sub

Once you MOVE the sheet, it is in a separate workbook. Do you do anything to THAT workbook/sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RyanScharfy]

Maybe this is a question for the Office forum. The code (your or mine), works fine the first time it's run. When I go back to my original workbook, there is a button where macro is assigned. Instead of pointing to Macro7, it points to Book27!Macro7.

Excel 2003 didn't have this quirk/feature. It's only popping up now that I've got 2007.

 

[GVF]

In your first post you say "...then separate a worksheet into a new workbook". Is the Button Code on the sheet that gets moved to a new workbook? Maybe an external link is being created. At the end of your code (after the worksheet gets moved to the other workbook) try adding code that resets the OnAction property of the button to the local version of the macro.
It wouldn't be the first time that someone in the state of Washington decided they knew what you wanted better than you knew what you wanted.