exporting sheets with buttons from master - VBA

[p4nny]

Hi,

I'm using the following code to export and save a number of sheets from a master workbook (rngTM contains the names of worksheets i wish to 'export'.)

One problem is that each sheet has a button with the task of refreshing the sheet..this works fine in the master but once i save in new location as an xlsm file, the buttons lose the code. I would like the newly created sheets to have the vba code within.. thanks in advance




Sub exportsheets()

Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String




On Error GoTo Errorcatch


Application.ScreenUpdating = False


strPath = "C:\Users\pandoan\Desktop\test\"
Set rngTM = Sheets("Flow TM's").Range("A1")


Do
Sheets(Array("HC pivot TM data", rngTM.Value)).Copy
Set wbNew = ActiveWorkbook
With wbNew
.Sheets("HC pivot TM data").Visible = False
Application.Goto .Sheets(1).Range("B13"), True

Range("b2, F5:G5", "T3:U3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wbNew.SaveAs strPath & rngTM & Format(Date, "ddmmmyyyy") & ".xlsm", FileFormat:=52

ActiveWorkbook.Close


End With
Set rngTM = rngTM.Offset(1, 0)
Loop Until IsEmpty(ActiveCell) = True

Application.ScreenUpdating = True


Errorcatch:
MsgBox Err.Description


End Sub

 

[igold]

Instead of trying to copy the button, why not just write a new button on your new sheet with something like:

ActiveSheet.Buttons.Add(1000, 10, 80, 18).Select
Selection.Name = "Refresh Button"
Selection.OnAction = "RefreshSheet"
Selection.Characters.Text = "Refresh Sheet"

And write a refresh sub to be called from the OnAction