Copy and paste formulas and pictures

**kadams99** · 03-21-2011, 02:29 PM

Hi,

I am trying to copy formulas and pictures from one workbook to another via VBA code. I've got the formula part working as follows:

  filetoopen = Application _
    .GetOpenFilename("Excel Files (*.xls*), *.xls*", , _
      "Select Old File from which to Import Data")
   Workbooks.Open filetoopen
   Set xlOld = ActiveWorkbook
   Set xlNew = ThisWorkbook

    xlOld.Sheets("Worksheet").Cells.Copy
    xlNew.Sheets("Worksheet").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

I can't seem to figure out how to copy the pictures on the original Worksheet tab to the new workbook Worksheet tab.

Can someone help me with this please?

Many Thanks,
Karyn

**Marcol** · 03-21-2011, 03:19 PM

Looks to me that you are copying a sheet as a template, if that is the case then try this

Delete xlNew.Sheets("Worksheet"). or don't add it in the first place.

Then run your code with this amendment

    Set xlOld = ActiveWorkbook
    Set xlNew = ThisWorkbook

    xlOld.Sheets("Sheet1").Copy Before:=xlNew.Sheets(1)

    Cells.Copy
    Cells.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, _
                       Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Hope this helps

**kadams99** · 03-21-2011, 03:45 PM

Actually, the new worksheet is the template that I want to copy the old worksheet data and pictures into. I don't want to delete this worksheet completely because it might have something new in it. What I want to accomplish is to copy the data and pictures from the old worksheet called "Worksheet" to the new worksheet called "Worksheet".

Thanks.

**Marcol** · 03-21-2011, 07:34 PM

Okay try running my suggestion then renaming the imported sheet, it should come in as "worksheet(2)".
If I am again reading your code correctly, you are over-writing all of the existing sheet with the old data, so why does it matter if you delete it?