Add a worksheet to the end of a workbook.

[Darren Bartrup]

Hi all,

I'm having trouble with adding a worksheet to the end of a workbook.
This is the code I'm using:

    Public Sub CopyChart()
    
        Dim cpyChart    As Variant
        Dim myChart As Variant
        Dim objPict As Object
        Dim wrkBk   As Workbook
        Dim NewWrkSht  As Worksheet
        
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Set wrkBk = Workbooks.Add
        
        For Each cpyChart In ThisWorkbook.Charts
            cpyChart.CopyPicture appearance:=xlScreen, Format:=xlBitmap
            '//Get an error on the next line:
            Set NewWrkSht = wrkBk.Sheets.Add(After:=wrkBk.Sheets.Count)
            NewWrkSht.Name = cpyChart.Name
            NewWrkSht.PasteSpecial Format:="Bitmap"
            With NewWrkSht.PageSetup
                .CenterHorizontally = True
                .CenterVertically = True
                .Orientation = xlLandscape
                .LeftMargin = Application.InchesToPoints(0.2)
                .RightMargin = Application.InchesToPoints(0.2)
                .TopMargin = Application.InchesToPoints(0.9)
                .BottomMargin = Application.InchesToPoints(0.9)
                .HeaderMargin = Application.InchesToPoints(0.5)
                .FooterMargin = Application.InchesToPoints(0.5)
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            Set NewWrkSht = Nothing
        Next cpyChart
        
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True

    End Sub

As I've commented in the code this line throws back an error:

Set NewWrkSht = wrkBk.Sheets.Add(After:=wrkBk.Sheets.Count)

The error is: Method 'Add' of object 'Sheets' failed.
I've tried removing the brackets, giving a value instead of sheets.count - nothing is pointing to the reason for the error.

Where am I going wrong?
Any help greatly appreciated as usual!

 

[Krishnakumar]

May be..

Set NewWrkSht = wrkBk.Sheets.Add(After:=Sheets(wrkBk.Sheets.Count))

 

[Darren Bartrup]

Works perfectly now.

Thanks for that Krishnakumar. I knew it had to be something obivious