Add a worksheet to the end of a workbook.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm having trouble with adding a worksheet to the end of a workbook.
This is the code I'm using:
Code:
    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:
Code:
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
May be..

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

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Works perfectly now.

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

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top