Add New Worksheet at the End

[SudhakarVE]

Hi,

I'm trying to add a new worksheet at the end of the workbook. The following add method adds the worksheet at the first sheet. VBA After:= with count is not working in test complete.

sWritePath="Z:\Expected Data Report Files\ComSnapShot\Sample.xlsx"

Set oXL = CreateObject("Excel.Application")

Set oVPRWB = oXL.Workbooks.Open(sMainpath)

Set oPCSExpWB= oXL.Workbooks.Open(sWritePath)

oPCSExpWB.Worksheets.Add

Appreciate any idea to add the new sheet at the end.

Regards,

Sudhakar

[Colin_McCrae]

Does this help?

http://stackoverflow.com/questions/14109102/how-do-i-add-a-worksheet-after-all-existing-excel-worksh...

[TanyaYatskovska · Accepted solution]

Hi Sudhakar,

The following code works for me:

sub Test1
  sWritePath="e:\Book1.xlsx"
  Set oXL = CreateObject("Excel.Application")
 
  Set oPCSExpWB= oXL.Workbooks.Open(sWritePath)
  Call oPCSExpWB.Worksheets.Add (, oPCSExpWB.Sheets(oPCSExpWB.Sheets.Count))
  oPCSExpWB.Save
  oXL.Quit
end sub

[SudhakarVE]

Hi:

This code works. Appreciate it

Thanks,

Sudhakar

[Colin_McCrae]

Thats pretty much identical to what it says on the Stack post I linked to ....