I am trying to export multiple charts in multiple excel sheets within same excel file.

[supriyamote]

but somehow, its is giving me error "Subscript out of range".

My default excel sheet count is 1.

Please find below the code i am using

sub ExcelFile

strDate = CDate(Date)
strDay = DatePart("d", strDate)
strMonth = DatePart("m", strDate)
strYear = DatePart("yyyy", strDate)
If strDay < 10 Then
strDay = "0" & strDay
End If

If strMonth < 10 Then
strMonth = "0" & strMonth
End If

GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "C:\Users\Desktop\Qlikview\Australia\Final Run\"
FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
set XLDoc = XLApp.Workbooks.Add


ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
XLDoc.Sheets(1).Paste()
XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
XLDoc.Sheets(2).Paste()
XLDoc.Sheets(2).Rows("1:3000").EntireRow.AutoFit

XLDoc.Sheets(1).Name = "Page One"
XLDoc.Sheets(2).Name = "Page Two"
XLDoc.Sheets(3).Delete

XLDoc.Sheets(1).Range("A1").Select

ActiveDocument.Save
ActiveDocument.GetApplication.Quit

end sub

[jonathandienst]

It would help if you could indicate which line is triggering the error. My guess is that the new Excel workbook is being created with a single tab, and the problem is occurring when you reference tab 2 (Sheets(2)) or 3. The number of tabs in a workbook is user configurable and it may not be the default of 3.

[supriyamote]

Yes..

Below line is giving error.

XLDoc.Sheets(2).Paste()

[jonathandienst]

You will need to add a line of code to create Sheets(2).
Add sheet in Excel

And remove the line deleting Sheets(3).