Excel vba: to export multiple excel sheet into its designated jpg file

microsoft excelmicrosoft-excel-2010vbaworksheet-function

I am working on an excel vba command to export multiple excel sheet to its designated jpg file.

I have 16 worksheet in one excel workbook and it would be best to export each sheet into jpg format.

I have the below code working, to go through all worksheet and create 16 jpg files. However it seems like excel is only capturing the worksheet that active. Say, if "Sheet1" is active, the whole 16 jpg files will show whatever is inside the "Sheet1" and not showing whatever is there on "Sheet2" or "Sheet3", etc.

Sub exportpic()

Dim WS As Worksheet, Inpt As Worksheet
Dim rgExp As Range
Dim CH As ChartObject

Set Inpt = Sheets("Input")
Set rgExp = Range("B5:M60")

    For Each WS In ThisWorkbook.Sheets
        If Not WS.Name = "Input" Then
            rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
            Set CH = WS.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
            CH.Chart.ChartArea.Select
            CH.Chart.Paste
            CH.Chart.Export "C:\Users\User1\Desktop\Umbilical\" & WS.Name & ".jpg"
            CH.Delete

        End If
    Next WS

End Sub

Can anybody please help me what is the right way to capture/export each sheet into its designated jpg file? Truly appreciate your kind assistance on the above.

Thanks very much in advance.

Best Answer

Just so this question doesn't appear unanswered - no answers, the issue was the definition of rgExp not including WS

Instead of changing:

rgExp.CopyPicture to WS.Range("B5:M60").CopyPicture and not using the rgExp variable at all.

You would change:

set rgExp = Range("B5:M60") to set rgExp = WS.Range("B5:M60")

This is in case you needed to use rgExp somewhere else.

Related Question