Export multiple object to multiple sheets - Excel

[#1]

Hi everyone,

I use this macro below to export multiple object to excel. What I need is to add some code to export more object to other sheet in excel. I would appreciate your help

sub test

'Set the path where the excel will be saved

filePath = "C:\Test.xlsx"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

'first chart object

Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH421","CH11", "CH12")

usedRows=0

For Each chart In chartArray

   Set i = ActiveDocument.GetSheetObject(chart)

    chartCaption = i.GetCaption.Name.v

    curSheet.Cells(usedRows+1, 1)=chartCaption

     i.CopyTableToClipboard true

     curSheet.Cells(usedRows+3, 1).Select

     curSheet.Paste

     usedRows=curSheet.UsedRange.Rows.Count+3 '--->function to get the first unused row

Next

'loop end

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

[whiteline]

Hi.

There is a list of objects to export in the line

chartArray = Array("CH421","CH11", "CH12")

You can simply add the id of objects that you want to export.

To get the object id, open object properties general tab, the top right corner.

[#3]

I know that, but I want to add object ("CH13","CH14","CH15") on sheet 2

[whiteline]

The sheet is assigned by number:

Set curSheet = curWorkBook.WorkSheets(1)

You can set the sheet #2 as curSheet and repeat (copy) the loop with appropriate objects.

[#5]

I tried this but he didnt copy object to another sheet

[#6]

You can try and send me macro if you succeed

[whiteline · Accepted solution]

Hi.

You have to make the sheet 'active' to perform copypaste.

Add curSheet.Activate before the loop.

It's really a VBA question.

You can find more information in MSDN, StackOverflow and other related resources.

[#8]

Tnx whiteline

Now it works, and you are right this is VBA question, but I assumed that someone did something like this