How to copy varying table range in excel and paste it in word as picture|VBA & Macros|Excel Forum|My Online Training Hub

Hi Sammy,

Without seeing your workbook I can't give you my 'best' answer.  I don't know what data you are trying to copy.

Perhaps you could format your data in a table and copy the table using the table name, or you could click into a cell in the range before running your macro and then copy it using ActiveCell.CurrentRegion.

Option Explicit

Sub CopyRangePic()

Dim tbl As Range
Dim exWB As Workbook
Dim Dest As Worksheet

Set Dest = ThisWorkbook.Sheets(2)

'Must first click into range you want to copy
ActiveCell.CurrentRegion.Select
Set tbl = Selection
tbl.CopyPicture
Dest.Paste

End Sub

Sub CopyTablePic()

Dim tbl As Range
Dim exWB As Workbook
Dim Dest As Worksheet

Set Dest = ThisWorkbook.Sheets(2)

Set tbl = Range("Table1")
tbl.CopyPicture
Dest.Paste

End Sub

Please see attached workbook for examples of both.

Regards

Phil