Export pivot table to .gif image file - buggy!

[wazimu13]

Hi

I have the following code which works in some cases but in others it generates an actual chart on the resulting image!
Researching this online has been very frustrating and am hoping you gurus can help How should the code be modified?

Basically what im trying to do is save several pivot tables as gif files which i then email. People ive asked say the best way
is to export as an image, and someone kindly offered this code.

Im quite new to VBA but cant see
which line is responsible for this behavior and having said that, why does it work flawlessly and all of a sudden just change.

In the attached images, the andy clarke.gif is the output i desire while the other image is how it goes wrong

Thanks in advance!

Sub CopyRangeToGIF()
' save a range from Excel as a picture

Dim rng As Excel.Range
Dim cht As Excel.ChartObject
Dim i As Integer

Const strPath As String = "C:\Users\paul\Documents\pics\"
 
Application.ScreenUpdating = False
 
 For i = 4 To Sheets.Count - 6

 Set rng = Sheets(i).UsedRange
 
rng.CopyPicture xlScreen, xlPicture
 
Set cht = Sheets(i).ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
 
cht.Chart.Paste
 
cht.Chart.Export strPath & Sheets(i).Name & ".gif"
 
cht.Delete
 
ExitProc:

Next i

Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub