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

[Gysbert_Wassenaar]

See Export Qlikview objects to multiple Excel sheet

[#3]

Tnx man, but I need to calculate number of rows and to set my next object to start two or three rows below previous one. Like in my macro above.

[Gysbert_Wassenaar]

What I need is to add some code to export more object to other sheet in excel.

So you are trying to find the other sheet by finding the first unused row on the current sheet?

Try using the .End method:

Const xlUp = -4162

curSheet.Range("a1").End(xlUp).Offset(1, 0).Select

[#5]

I want to create new sheet and to put on him some new object, and then to create new sheet and to put on him some new object....