Macros - copying sheet to another workbook

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
115
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have made a Daily log (workbook) with a worksheet as a handover sheet for work. There is vba code that copies the Handover worksheet from the current shift to the oncoming's shifts log (workbook) and saves the oncoming Daily Log and closes it. This all works great. I have made a couple of macros within the Handover sheet but when it copies on to the oncoming shifts workbook (daily log) the macros do not work. The code is there but you have to reapply the macro to the button. Is there any way around this?

Thanks all
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
There is vba code that copies the Handover worksheet from the current shift to the oncoming's shifts log (workbook) and saves the oncoming Daily Log and closes it.
In this process you have to change the OnAction property of the buttons involved, assuming they're not ActiveX buttons.
 
Upvote 0
In this process you have to change the OnAction property of the buttons involved, assuming they're not ActiveX buttons.
Hi @GWteB No they’re no ActiveX buttons, just a insert, shape and then a recorded macro attached to it. That’s in the properties in the vba editor isn’t it. Thank you for replying.
 
Upvote 0
Due to the fact that you use a macro to copy the worksheet to another workbook, I think it's obvious that you also make that macro responsible for changing all buttons present on that worksheet rather than changing the needed assignments manually. The code below can be used for the automatic reassignments.

VBA Code:
Public Sub ChangeButtonOnAction(ByVal argSht As Worksheet)
    ' reassign the current macro name to the OnAction property
    ' of all existing buttons or shapes on the given worksheet
    Dim shp As Shape, s As String, p As Long
    For Each shp In argSht.Shapes
        s = shp.OnAction
        p = InStr(1, s, "!")
        If Len(s) > 0 Then
            If CBool(p) Then
                s = Right(s, Len(s) - p)
            End If
            s = "'" & VBA.Replace(argSht.Parent.Name, "'", "''") & "'!" & s
        End If
    Next
End Sub

Usage example:
VBA Code:
' you may have code like this to perform the copy of the desired worksheet
ThisWorkbook.Worksheets("Sheet1").Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)

' in that case the sheets object can be passed on to the macro responsible for reassigning the macro names
ChangeButtonOnAction wbTarget.Sheets(wbTarget.Sheets.Count)

Or in a similar way:
VBA Code:
ThisWorkbook.Worksheets("Sheet1").Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)

Dim Sht As Worksheet
Set Sht = wbTarget.Sheets(wbTarget.Sheets.Count)
ChangeButtonOnAction Sht
 
Upvote 0
Thank you - I'll have a look at what the code I have is using and see where I can fit this in.
 
Upvote 0
Ok. Don't hesitate to post back if you're having issues.
 
Upvote 0
Solution
Thank you. I used an ActiveX button setup and it worked so that was great. Thanks for the direction. Can I ask if anyone knows how to get rid of the 3d / white type surround of the edge of the ActiveX button?
 
Upvote 0
Glad it's sorted.

Can I ask if anyone knows how to get rid of the 3d / white type surround of the edge of the ActiveX button?
As far as I know that's not possible. If you dont want the 3D appearance you can use a label instead.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top