Caros, bom dia!
Tenho a macro abaixo e gostaria que ela gerasse um relatório para cada setor que tenho na tabela e gerasse com um nome específico. Ex.: BRZ001Lista.xls. Sendo o 001 um setor.
É possível?
Sub REPORT_EXCEL
'Set the path where the excel will be saved
filePath = "C:\Teste.xls"
'Create the Excel spreadsheet
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
'Create the WorkBook
Set curWorkBook = excelFile.WorkBooks.Add
'Create the Sheet
Set curSheet = curWorkBook.WorkSheets(1)
'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("TB01")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTableToClipboard true
'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption
'Set the first cell with the caption
curSheet.Range("A1") = chartCaption
'Paste the rest of the chart
curSheet.Paste curSheet.Range("A2")
excelFile.Visible = true
'Save the file and quit excel
curWorkBook.SaveAs filePath
curWorkBook.Close
excelFile.Quit
'Cleanup
Set curWorkBook = nothing
Set excelFile = nothing
End Sub