Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy sheet with macro on (Excel 2000)

    I have a test sheet that I have put a macro on, (at present on the sheet rather than in a module). I want to copy the sheet to another book and then rename the macro that is assigned to the button on the sheet so that it looks at the new macro on the current sheet in the current workbook. The message box at the end of the code tells me I have succeeded, but the reality is - I still have the old workbook referenced when I look more closely.

    What am I doing wrong? Example code ...

    Sub copyme()
    Dim holdsheetname
    Dim holdmacroname As String
    Dim newmacroname As String

    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy
    holdsheetname = ActiveSheet.Name
    ActiveSheet.Shapes("Button 1").Select
    holdmacroname = Selection.OnAction
    newmacroname = Right(holdmacroname, Len(holdmacroname) - Application.WorksheetFunction.Find("!", holdmacroname))
    ActiveSheet.Shapes("button 1").Select
    Selection.OnAction = newmacroname

    MsgBox (newmacroname)


    End Sub
    Neil Eustice
    Woody Worshipper

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    Maybe I misunderstand, but if all the code is contained in the sheet module, copying the sheet should not require changing procedure names.

    Does the code that your command button calls reside in the same sheet codemodule as the button itself ?

    Is the Button created from the Control Toolsbox or th e Forms Toolbox ?

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    The code all lives in the sheet module and the button was drawn from the Forms toolbar.
    Neil Eustice
    Woody Worshipper

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    Why do you feel you need to change the name of the procedure ?

    Do you get some error whilst running the code ? In what wayis the old workbook referenced ?

    Did your code in the original workbook refer to ranges or objects on a sheets other than the sheet containing the code ?

    How many procedures are in the worksheet module ?

    Sorry for all the questions.

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    Why do you feel you need to change the name of the procedure ? Because at present I have sheet1 with a button and a macro attached, say the macro is called "firstmacro". The button says it has Sheet1.firstmacro attached. As soon as I copy the sheet, to a new workbook, the button on the new worksheet says 'Workbook1.xls'!sheet1.firstmacro. I don't want the new sheet to reference the old one as I am sending the new sheet to someone else, and they wont have access to the old workbook.

    Do you get some error whilst running the code ? In what wayis the old workbook referenced ?No error, referenced as above.

    Did your code in the original workbook refer to ranges or objects on a sheets other than the sheet containing the code ? Referred to ranges on the same sheet.

    How many procedures are in the worksheet module ?It may vary over time!
    Neil Eustice
    Woody Worshipper

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    Neil,

    Try the following as a replacement for your CopyMe routine :<pre>Sub CopyMe()
    Dim strNewMacro As String
    ActiveSheet.Copy
    strNewMacro = ActiveWorkbook.Name & "!Sheet1.Macro1"
    ActiveSheet.Shapes("Button 1").OnAction = NewMacro
    End Sub</pre>


    Hope it is what you want,

    Andrew C

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    OK, this works, thankyou. The main problem though, is that I wish to put this macro on several sheets where the button on each sheet will have a different macro assigned to it. I was hoping that rather than mention each macro individually, I could somehow pick up the name of the macro assigned to a button and strip off the workbook references so it just looked locally each time. Is this possible?
    Neil Eustice
    Woody Worshipper

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy sheet with macro on (Excel 2000)

    Try the following :<pre>Sub CopyMe()
    Dim shp As Shape
    Dim NewBookRef As String
    ActiveSheet.Copy
    NewBookRef = ActiveWorkbook.Name & "!"
    For Each shp In ActiveSheet.Shapes
    If shp.OnAction <> "" Then
    strMac = shp.OnAction
    shp.OnAction = NewBookRef & _
    Right(strMac, Len(strMac) - _
    InStr(1, strMac, "!"))
    End If
    Next
    End Sub</pre>


    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •