macro to copy and paste data to new sheet

Here is you sample back, click the button and the sheets will generate, I have been trying for hours to get each sheet range to save as a .gif but I have run out of time trying to get them to work in a loop, check out these two sites they are close to what you want.

The Workbook is saved as XL'03

http://www.andypope.info/vba/gex.htm
http://www.mvps.org/dmcritchie/excel/xl2gif.htm

Here is a new workbook, a solution to autofit the Chart is still needed.
You will need a Folder Named "Excel" and a Sub folder named "Calendar"
"C:\Excel\Calendar\"

Run the code, once the code is finished, check the above folder for the .Gif files.

This will resize the pasted shape according to the longest dimension.

Sub SaveAsGif()
    Dim ws As Worksheet
    Dim chtHolder As Chart
    Dim lngIndex As Long
    
    Set chtHolder = Charts.Add
    Do While chtHolder.SeriesCollection.Count > 0
        chtHolder.SeriesCollection(1).Delete
    Loop
    
    For lngIndex = ActiveWorkbook.Worksheets.Count To 1 Step -1
        Set ws = ActiveWorkbook.Worksheets(lngIndex)
        If ws.Name <> "Sheet1" Then
            ws.Select
            ws.Range("A1", ws.Range("B65536").End(xlUp)).CopyPicture Appearance:=xlScreen, Format:=xlBitmap
            chtHolder.Select
            chtHolder.Paste
            
            ' autosize shape
            With chtHolder.Shapes(1)
                .LockAspectRatio = True
                If .Width > .Height Then
                    .Width = chtHolder.ChartArea.Width
                Else
                    .Height = chtHolder.ChartArea.Height
                End If
            End With
            chtHolder.Export Filename:="C:\temp\" & ws.Name & ".gif", FilterName:="GIF"
            
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            chtHolder.Shapes(1).Delete
        End If
    Next
    Application.DisplayAlerts = False
    chtHolder.Delete
    Application.DisplayAlerts = True

End Sub

mypassion1,

You have changed the workbook, and the example I have supplied works for your original workbook, because it looks for the word "Max".

The problem is, you can't just export a range in a worksheet as a .Gif. without additional help.
Andy Pope's Addin works well, Dave McRichie's code works as well (links supplied in previous post), to selecting a range and saving that range as a .Gif, but I could not automate it to work in a worksheet loop.

You can though, export a chart as a .Gif, and that is what I have been working on. The example supplied, copies each worksheet range as a .Gif and places it into a chart, the chart is then exported as a .Gif to the folder I originally mentioned in the previous post. Andy's revised code makes sure that the "Worksheet" picture fits in the chart, so that there is no missing information outside of the chart before it gets exported.

Why the .Gif's come out as blank is probable a windows issue, you should exit Excel and then open the workbook, sometimes if you open a workbook with the same name as a previous open workbook, the previous version is what is opened, especially if you are downloading it from the internet.

Thanks Andy for the revised code.