CODEWITHSUNDEEP
excelvbscript
tjshah050291
tjshah050291

Reputation: 71

How do I add a worksheet after all existing Excel worksheets?

I need to add an Excel sheet at the end of the current sheet number 3. When I run the below program, however, I get a sheet at the first position. How can I resolve this?

The program basically copies data from one Excel workbook to another workbook, consisting of multiple sheets.

Code:

Dim objXL,objWrkBk,objWrkSht,a,n
Set objfso=CreateObject("Scripting.FileSystemObject")
Set objXL=CreateObject("Excel.Application")
Set objWrkBk=objXL.Workbooks.Open("C:\learning\demo.xlsx")
m=objWrkBk.Worksheets.count
msgbox m
For n=1 to m
Set objWrkBk=objXL.Workbooks.Open("C:\learning\demo.xlsx")
Set objWrkSht=objWrkBk.Worksheets("Sheet"&n)
columncount = objWrkSht.usedrange.columns.count
rowcount = objWrkSht.usedrange.rows.count
For i=1 to rowcount
For j=1 to columncount
If   objWrkSht.cells(i,j).value <> "" Then
    a= objWrkSht.cells(i,j).value& "   "
End If
'Next
'Next
check=objfso.FileExists("C:\learning\demo1.xlsx")
If not check Then
    objXL.Workbooks.Add
    objXL.ActiveWorkbook.SaveAs("C:\learning\demo1.xlsx")
End If
Set objWrkBk1=objXL.Workbooks.Open("C:\learning\demo1.xlsx")
If n<=3 Then
    Set objWrkSht1=objWrkBk1.Worksheets("Sheet"&n)
End If
If n>3 Then
    objXL.Worksheets.add
    Set objWrkSht1=objWrkBk1.Worksheets("Sheet"&n)
End If

If   objWrkSht.cells(i,j).value <> "" Then
objWrkSht1.cells(i,j).value=a
objWrkBk1.Save
End If
Next
Next
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objWrkSht1=Nothing
Set objWrkBk1=Nothing

Next
objXL.Quit
Set objXL=Nothing

Upvotes: 2

Views: 30105

Answers (2)

George Birbilis
George Birbilis

Reputation: 2930

seems Worksheets property is read-only

Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

https://msdn.microsoft.com/en-us/library/office/ff835542(v=office.15).aspx

whereas Sheets is the real thing where you can also add Sheets dynamically

A collection of all the sheets in the specified or active workbook.

https://msdn.microsoft.com/en-us/library/office/ff193217(v=office.15).aspx

Upvotes: 0

brettdj
brettdj

Reputation: 55672

You can add sheets in the After position, ie this code adds the sheet after the last worksheet (provided by objWrkBk.Sheets(objWrkBk.Sheets.Count)) where objWrkBk.Sheets.Count) is the number of sheets prior to the addition.

Set objXL = CreateObject("Excel.Application")
Set objWrkBk = objXL.Workbooks.Add
Set objWrkSht = objWrkBk.Sheets.Add(, objWrkBk.Sheets(objWrkBk.Sheets.Count))

Upvotes: 9

Related Questions