Copy/Paste values, formats, and pictures (not formulas)

Question

I have a range (in a different workbook) that I want values, formats, and pictures copied into the a different range (active workbook with vba scipts). The copy/paste occurs multiple times up to a max of 50 (each is one page ao there are 50 pages max). I've gotten to work xlPasteValues and xlPasteFormats, with the pictures pre-copied in the 50 ranges (the pictures are identical, repeats in same locations for each range). The workbook takes a bit of space and I'd like to trim its base size by copying the in the pictures instead of having 50 pre-copied sets that most are not used.

The previous code looked like this (that doesn't copy the pictures - I used to have all pictures already copied, but this takes up space):

Set rngImportCopyRange = Range(wksImportedPeakHour.Cells(1, 1), wksImportedPeakHour.Cells(65, 34))
rngImportCopyRange.Copy
wksStartHere.Cells(intRowStartHere, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
rngImportCopyRange.Copy
wksStartHere.Cells(intRowStartHere, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Where "wksImportedPeakHour" is the sheet that has the range being copied (always in the same spot, has data in columns to right that I don't want) and "intRowStartHere" is an integer that keeps track of the row where to paste the range.

I've tried adding on "Application.CopyObjectsWithCells = True" just before xlPasteFormats, but it errors trying to do that.

A straight copy/paste with the following code copies everything, but the formulas of the other workbook get copied too rather than the values (not okay):

rngImportCopyRange.Copy wksStartHere.Cells(intRowStartHere, 1)

Now, I could copy everything, then unmerge cells (some are cells are merged and I can't paste special xlPasteValues when merged), then paste the values, and then paste formulas - that looks like the following:

Set rngImportCopyRange = Range(wksImportedPeakHour.Cells(1, 1), wksImportedPeakHour.Cells(65, 34))
rngImportCopyRange.Copy wksStartHere.Cells(intRowStartHere, 1)

Set rngUnMerge = Range(wksStartHere.Cells(intRowStartHere, 1), wksStartHere.Cells(intRowStartHere + 64, 34))
rngUnMerge.UnMerge

rngImportCopyRange.Copy
wksStartHere.Cells(intRowStartHere, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

rngImportCopyRange.Copy
wksStartHere.Cells(intRowStartHere, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

But having the above is three copy/paste operations and an un-merge. I would hope that there is some special operation or trick that allows the script to not run as long as it does. I appreciate your help, thanks!

CWilson CWilson · Accepted Answer · 2016-04-26T14:08:12

If you have one copy of the image inside a shape, already embedded in your workbook (Application.sheets(x).Shapes.AddPicture), you just copy the shape (not the image) to whatever sheet and cell you want it in. There are ways to do it without shapes, but some versions of Excel don't like those ways.

Copy/Paste values, formats, and pictures (not formulas)

1 Answers