0

i'm trying to export all my sheets in my workbook at once as image. The final goal should be to open the macro or press an button and then all the worksheets are exported to an directory named as "worksheet".jpg and they should have the exact same size, for example. I tried to add some extra code to this, which i found somewhere:

Sub SaveStaticImageWorkbook()
Dim ws As Worksheet, wb As Workbook, fDialog As FileDialog
Application.DisplayAlerts = False
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Tmp123"
For Each ws In ThisWorkbook.Worksheets
    ws.Copy After:=wb.Sheets(wb.Sheets.Count)
Next ws
'Remove Sheet1
wb.Sheets("Tmp123").Delete
For Each ws In wb.Worksheets
    ws.Range(ws.Cells(1, 1), ws.Cells.SpecialCells(xlLastCell)).Copy
    ws.Select
    ws.Cells(1, 1).Select
    ws.Pictures.Paste
    ws.Range(ws.Cells(1, 1), ws.Cells.SpecialCells(xlLastCell)).Clear
Next ws

Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
fDialog.Title = "Save Static Workbook"
fDialog.InitialFileName = ThisWorkbook.Path
If fDialog.Show = -1 Then
  wb.SaveAs fDialog.SelectedItems(1)
End If
wb.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

My problem is, that i have no idea, where i have to add this extra code. I have tried several options, for example to add it in the for loop, but this fails often in runtime errors. Maybe someone of you can help me.

Thanks

Markus Bücher
  • 1
  • The typical way to export an Excel range as an image is by using a temporary chartobject to do the exporting. Eg see: https://software-solutions-online.com/excel-vba-save-table-as-jpeg/ – Tim Williams Nov 12 '21 at 23:17
  • Add a command button from the [Developer tab](https://www.educba.com/developer-tab-in-excel/). I tested your code and it works, but you need to understand the risk with using `xlLastCell`. You probably find many articles here on SO (e.g. https://stackoverflow.com/q/47550530/2292722 ). – Tom Brunberg Nov 13 '21 at 07:51

0 Answers0