Hi, I currently have the script below to copy a wksheet to a New Workbook. R9096 is 1 of 35 reports and they have their own worksheet. I would like the user to have an option of selecting the reports they want to extract. Is this possible to do within a macro? Maybe a drop menu showing the worksheets? Or is there a simpler way?
Sub test1()
'
' test1 Macro
'
'
Sheets("R9096").Select
Sheets("R9096").Copy
ActiveWorkbook.BreakLink Name:= _
"C:\Users\philipss\Fremantle\Finance - IP - Improvement Projects\Producer Share\00 Digital Income Report - TEST NEW.xlsm" _
, Type:=xlExcelLinks
Range("H3:M7").Select
Selection.ClearContents
ActiveSheet.Buttons.Delete
Range("E3").Select
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
For Each link In wb.LinkSources(xlExcelLinks)
wb.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\philipss\Fremantle\Finance - IP - Improvement Projects\Producer Share\Export Files\test.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub
Thanks, Steve
Sub test1()
'
' test1 Macro
'
'
Sheets("R9096").Select
Sheets("R9096").Copy
ActiveWorkbook.BreakLink Name:= _
"C:\Users\philipss\Fremantle\Finance - IP - Improvement Projects\Producer Share\00 Digital Income Report - TEST NEW.xlsm" _
, Type:=xlExcelLinks
Range("H3:M7").Select
Selection.ClearContents
ActiveSheet.Buttons.Delete
Range("E3").Select
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
For Each link In wb.LinkSources(xlExcelLinks)
wb.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\philipss\Fremantle\Finance - IP - Improvement Projects\Producer Share\Export Files\test.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub
Thanks, Steve