1

I would like to write a macro that takes a range of cells, copies them as an image, and then store this image into an picture or shape variable that I can pass into a subsequent method call.

Call Sheet6.Range("G1:V33").CopyPicture(xlScreen, xlPicture)

So far, using a tutorial, I have this line, though I am a bit unsure of what exactly the call tag is doing, and if I can use this way to later store it into a shape or picture named heatMap

Thank you in advance.

Rivers31334
  • 644
  • 1
  • 12
  • 30
  • Probably better to temporarily store it as a shape or chart object with a known name somewhere in the workbook. Recovering it from a known location and name would be easy at a later time. –  Feb 23 '16 at 16:58
  • Would you mind expounding upon this? What do you mean when you say 'temporarily'? – Rivers31334 Feb 23 '16 at 17:02
  • By 'temporarily', I mean that you are likely to delete it after using a copy of it somewhere else. –  Feb 23 '16 at 17:04

1 Answers1

2

Once you have copied the range, your destination for the picture is what determines how you paste it. For example, if you are pasting the picture into Excel then you just use

Worksheets("YourWorkbook").Range("YourRange").Pictures.Paste.Select

Note: the .Select isn't necessary unless you want to select the picture after pasting it.

Here is a way to paste it into Outlook

Here is how to paste it into Word

The Call keyword just makes it mandatory to provide the parentheses items. This previously answered question explains it well

EDIT in regards to comment: I would suggest using a For loop in regards to shapes on your worksheet. Use .count to find the number of shapes on your worksheet and create an array (Such as ShapeCount(1 to NumberOfShapes) to "collect the shapes" then you can call any of the shapes later on based on its array value.

Community
  • 1
  • 1
Dan
  • 425
  • 2
  • 13
  • Hmm, maybe I should add. I have shapes in my sheet that i wish to copy. I want to somehow collect these shapes (the shapes are US states that have been fit together in a map of the continental US...so 48 distinct shapes). – Rivers31334 Feb 23 '16 at 17:25