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 includingWS
Instead of changing:
rgExp.CopyPicture
toWS.Range("B5:M60").CopyPicture
and not using the rgExp variable at all.You would change:
set rgExp = Range("B5:M60")
toset rgExp = WS.Range("B5:M60")
This is in case you needed to use
rgExp
somewhere else.