Excelgeek

If you work with large amounts of data in Excel, it is likely that you have needed to copy a selected range of cells to a new workbook at some point. While you can manually copy and paste the range, this process can be time-consuming and error-prone, especially if you need to repeat the task frequently.

Fortunately, there are several ways to automate the process of copying a selected range to a new workbook in Excel. In this post, we will introduce two methods to achieve this task – the first one is the manual method, and the second one is using VBA code with Application.InputBox to select the range.

Copy a Selected Range to a New Workbook Manually

If you want to copy a selected range to a new workbook manually in Excel , you can follow these steps:

Step1: Select the range of cells you want to copy.

Step2: Press Ctrl + C on your keyboard to copy the selected range.

Step3: Click on the File tab in the ribbon menu, then select New to create a new workbook.

Step4: In the new workbook, click on the first cell where you want to paste the copied range.

Step5: Press Ctrl + V on your keyboard to paste the copied range.

Copy a Selected Range to a New Workbook with VBA Code

You can also use the VBA Code to copy a selected range to a new workbook in Excel, it will use the Application.Inputbox function to select the range. Just do the below steps:

Step1: Open the Excel workbook that contains the range you want to copy.

Step2: Press Alt + F11 on your keyboard to open the Visual Basic Editor.

Step3: In the Visual Basic Editor, click on Insert from the top menu and select Module to insert a new module.

Step4: Copy and paste the VBA code provided in the module.

Sub CopyRangeToNewWorkbook_excelgeek()
    Dim myRange As Range
    Dim newWorkbook As Workbook
    
    'Prompt user to select range to copy
    Set myRange = Application.InputBox(prompt:="Select the range to copy", Type:=8)
    
    'Create a new workbook
    Set newWorkbook = Workbooks.Add
    
    'Copy the selected range to the new workbook
    myRange.Copy Destination:=newWorkbook.Sheets(1).Range("A1")
    
    'Activate the new workbook
    newWorkbook.Activate
End Sub

Step5: Press F5 or Run from the top menu to run the macro.

Step6: In the pop-up window, select the range you want to copy by clicking and dragging your mouse over the cells. Click on the OK button to start the macro.

Step7: Excel will create a new workbook and copy the selected range to the new workbook.

This tutorial will introduce three methods for saving an Excel table as an image in Excel: “Paste as picture”, “Paint tool”, and “VBA code“.

The Paste as picture method allows you to create a picture of a range that can be easily inserted into documents or presentations.

The Paint tool method uses the built-in Windows Paint program to save the selected range as an image.

The VBA code method automates the process of saving the selected range as an image using Visual Basic for Applications (VBA) code.

Save Table as Image using Paste as Picture

You can save an Excel table as an image using the “paste as picture” feature. Here are the steps:

Step1: Select the table that you want to save as an image.

Step2: Right-click on the selected cells and choose “Copy” from the context menu or press Ctrl+C on your keyboard.

Step3: select one destination cell to place the image.  Then click the Home tab, click on the drop-down menu list from the Paste option in the Clipboard group.  Click on the Picture option.

Now you can simply copy the image and paste it to other application.

Save Table as Image using Paint Tool

You can save an Excel table as an image using the Paint tool in Windows. Here are the steps:

Step1: Select the table that you want to save as an image.

Step2: Right-click on the selected cells and choose “Copy” from the context menu or press Ctrl+C on your keyboard.

Step3: Open the Paint tool by typing “Paint” in the Windows search bar and selecting the app from the search results.

Step4: In Paint, choose “Paste” from the “Home” tab or press Ctrl+V on your keyboard to paste the copied table into the canvas.

Step5: Choose “Save As” from the “File” menu and select the desired file format (e.g. JPEG, PNG, BMP, etc.).

Step6: Enter a file name and choose a save location. Click “Save” to save the image.

Save Table as Image with VBA Code in Excel

You can use VBA code in Excel to save a selected table as an image and prompt the user to select a destination to save the image. Just do the following steps:

Step1: Open your Excel workbook.

Step2: Press Alt + F11 to open the VBA editor.

Step3: In the VBA editor, select Insert from the menu bar, then choose Module to create a new module.

Step4: Copy the VBA code I provided and paste it into the new module. Save the workbook and close the VBA editor.

Sub sleep(T As Single)
    Dim time1 As Single
    time1 = Timer
    Do
        DoEvents
    Loop While Timer - time1 < T
End Sub
Sub SaveTableAsImage_excelgeek()
    Dim tbl As Range
    Dim savePath As String, fileName As String, fileFormat As String
    Dim cht As ChartObject
      
    'Prompt the user to select the table to save as an image
    Set tbl = Application.InputBox("Select the table to save as an image:", Type:=8)

    tbl.CopyPicture xlScreen, xlPicture
 
     'prompt user for save location and file name
    savePath = Application.GetSaveAsFilename(InitialFileName:="table1", _
        FileFilter:="JPEG (*.jpg), *.jpg, PNG (*.png), *.png")
    If savePath = "False" Then Exit Sub ' exit if user clicks cancel
     
    'extract file name and format from save path
    fileName = Mid(savePath, InStrRev(savePath, "\") + 1)
    fileFormat = Mid(fileName, InStrRev(fileName, ".") + 1)
    fileName = Left(fileName, InStrRev(fileName, ".") - 1)
    
    
    'create temporary chart object, set dimensions to table dimensions, paste table inside, and save as image
    Set cht = ActiveSheet.ChartObjects.Add(0, 0, tbl.Width, tbl.Height)
    Call sleep(2)
    cht.Chart.Paste
    cht.Chart.Export savePath, fileFormat
    cht.Delete
    Application.CutCopyMode = False
    
End Sub

Step5: Press Alt + F8 to open the Macros dialog box. Select the SaveTableAsImage_excelgeek macro from the list and click the Run button.

Step6: Select the table to save as an image.

Step7: select one the location to place image.

Step8: The picture will be saved into the selected location.

Conclusion

There are several methods available for saving an Excel table as an image, each with its own advantages and limitations. The “Paste as picture” method is quick and easy, but may result in lower image quality. The “Paint tool” method provides greater control over image quality, but requires an extra step. The “VBA code” method offers the most automation and customization options, but may require some programming knowledge.

This post will introduce two methods to copy and paste a range or chart object as a picture in Excel. The first method involves using Excel’s built-in “Copy as Picture” feature, while the second method uses VBA code to automate the process.

The VBA code method will select a range or chart object using Application.Inputbox function and choose a destination cell for the picture. The VBA code will then copy the range or chart object as a picture and paste it into the specified cell.

Copy and Paste Range or Chart object as Picture using Copy as Picture

You can use the “Copy as Picture” feature in Excel to copy a range or chart object as a picture. Here are the steps:

Step1: Select the range or chart object that you want to copy as a picture.

Step2: Go to the “Home” tab in the Excel Ribbon. Click the drop-down arrow next to the “Copy” button in the “Clipboard” section. Select “Copy as Picture” from the drop-down menu. The Copy Picture dialog box will open.

Step3: In the “Copy Picture” dialog box, select the Format as Picture, and select Appearance as shown on screen.

Step4: Click the “OK” button to copy the range or chart object as a picture.You can paste the picture wherever you want by pressing “Ctrl + V” on the keyboard.

Copy and Paste Range or Chart object as Picture with VBA Code

You can also use the VBA Code to copy a range or chart object and paste it in your Excel worksheet, just do the following steps:

Step1: Open your Excel workbook.

Step2: Press Alt + F11 to open the VBA editor.

Step3: In the VBA editor, select Insert from the menu bar, then choose Module to create a new module.

Step4: Copy the VBA code I provided and paste it into the new module. Save the workbook and close the VBA editor.

Sub CopyAsPicture_excelgeek()
    Dim rng As Range
    Dim chartObj As ChartObject
    Dim destCell As Range
    
    'Prompt user to select range or chart object
    On Error Resume Next
    Set rng = Application.InputBox("Select a range or chart object to copy as picture", Type:=8)
    On Error GoTo 0
    
    'If user selects a range, copy it as picture
    If Not rng Is Nothing Then
        rng.CopyPicture xlScreen, xlPicture
        
        'Prompt user to select destination cell
        On Error Resume Next
        Set destCell = Application.InputBox("Select destination cell to place picture", Type:=8)
        On Error GoTo 0
        
        'If user selects a cell, paste the picture and adjust its size
        If Not destCell Is Nothing Then
            With destCell.Parent.Pictures.Paste
                .Left = destCell.Left
                .Top = destCell.Top
                .Width = rng.Width
                .Height = rng.Height
            End With
        End If
    'If user selects a chart object, copy it as picture
    ElseIf ActiveChart Is Nothing Then
        MsgBox "Please select a range or chart object."
    Else
        Set chartObj = ActiveSheet.ChartObjects(ActiveChart.Parent.Name)
        chartObj.Chart.Export Environ$("temp") & "\temp.png", "PNG"
        With destCell.Parent.Pictures.Insert(Environ$("temp") & "\temp.png")
            .Left = destCell.Left
            .Top = destCell.Top
            .Width = chartObj.Width
            .Height = chartObj.Height
        End With
        Kill Environ$("temp") & "\temp.png"
    End If
End Sub

Step5: Press Alt + F8 to open the Macros dialog box. Select the CopyAsPicture_excelhow macro from the list and click the Run button.

Step6: Select the cell where you want to paste the picture.

Step7: select one the destination cell to place picture.

Step8: The picture will be pasted into the selected cell.

This post will introduce you to two methods of converting a range of cells to an image in Excel. The method involves using the “Copy As Picture” feature in Excel and a VBA code to export the copied range as an image file.

Convert Cells Range to Image Using Copy as Picture Feature

If you want to convert a range of cells to an image in Microsoft Excel Spreadsheet, you can use the built-in “Copy As Picture” feature. Here are the steps:

Step1: Select the cells range that you want to convert to an image.

Step2: Press the “Ctrl + C” shortcut on your keyboard to copy the cells range to the clipboard.

Step3: Click on the “Home” tab in the ribbon. Click on the “Copy” dropdown arrow in the “Clipboard” group, and then select “Copy as Picture“. The Copy Picture dialog box will open.

Step4: In the “Copy Picture” dialog box, select the “As shown on screen” option and the “Picture” format. Click on the “OK” button.

Step5: Now you can paste the copied cells range as an image in another location. For example, you can open an image editor (such as Microsoft Paint), and press the “Ctrl” and “V” keys on your keyboard to paste the image.

Convert Cells Range to Image with VBA Code

You can also use VBA code to automate the process of converting a range of cells to an image. Just do the following steps:

Step1: Open your workbook that contains the range of cells you want to convert to an image.

Step2: Press Alt + F11 to open the Visual Basic Editor.

Step3: In the Visual Basic Editor, click on Insert > Module to create a new module.

Step4: Copy the below VBA code and paste it into the new module.

Sub ConvertRangeToImage_excelgeek()
    Dim MyChart As ChartObject
    Dim MyRange As Range
    Dim DestPath As String
    Dim FileName As String
    
    Set MyRange = Application.InputBox("Select the range of cells:", Type:=8)
    MyRange.CopyPicture xlScreen, xlPicture
    
    Set MyChart = ActiveSheet.ChartObjects.Add(0, 0, MyRange.Width, MyRange.Height)
    
    MyChart.Activate
    MyChart.Chart.Paste
    DestPath = Application.GetSaveAsFilename(InitialFileName:="MyImage", _
        FileFilter:="PNG Files (*.png), *.png, JPEG Files (*.jpg), *.jpg, GIF Files (*.gif), *.gif, BMP Files (*.bmp), *.bmp", _
        Title:="Save As")
    If DestPath <> "False" Then
        FileName = Mid(DestPath, InStrRev(DestPath, "\") + 1)
        MyChart.Chart.Export DestPath, Mid(DestPath, InStrRev(DestPath, ".") + 1)
    End If
    MyChart.Delete
End Sub

Step5: Save the module and close the Visual Basic Editor. Press Alt + F8 to open the Macro dialog box.Select the ConvertRangeToImage_excelgeek macro from the list of available macros and click Run.

Step6: select the range of cells you want to convert to an image.

Step7: The code will prompt you to choose the destination folder and image name for the converted image.

Step8: Once you select the destination folder and image name, the code will export the range of cells as an image file in the selected format.

Conclusion

 With the above methods, you can easily convert any range of cells in your Excel worksheet to a PNG, JPG or other image format, which can be useful for sharing data, creating reports, or including the data in a presentation.