In Excel 2007 SP1, I want to copy a range with a picture using VBA.
The following is the VBA code. There is a picture at cells D1:D3. I
used Copy or AutoFill method to copy the range. When I ran this code,
the cells was copied 4 times correctly. But the picture was copied
only 1 time. I tried this code in Excel 2000 - 2003, it is correct.
What is the problem? Is it a bug of Excel 2007?

Best regards,
Liu Jianzhong


Sub test()
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

'Get Excel sheet
On Error GoTo llExcelSheetErr
Set xlWorkBook = ActiveWorkbook
Set xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
xlWorkSheet.Range("5:20").Insert xlShiftDown
' xlWorkSheet.Range("1:4").AutoFill xlWorkSheet.Range("1:20"),
xlFillCopy
xlWorkSheet.Range("1:4").Copy xlWorkSheet.Range("5:20")
Exit Sub

llExcelSheetErr:
Call MsgBox(Err.Description, vbExclamation, "VBA Test")
End Sub

The picture was not copied. A picture is not part of the cell structure it
is only an object that sits ontop of the cells and does not get copied. You
need to copy the picture and then place the copy at a new location.

Set MyPicture = ActiveSheet.Pictures("PictureofLincoln")
MyPicture.Copy
ActiveSheet.Paste
Set newpicture = Selection
newpicture.Top = Range("B7").Top
newpicture.Left = Range("B7").Left

" wrote:

In Excel 2007 SP1, I want to copy a range with a picture using VBA.
The following is the VBA code. There is a picture at cells D1:D3. I
used Copy or AutoFill method to copy the range. When I ran this code,
the cells was copied 4 times correctly. But the picture was copied
only 1 time. I tried this code in Excel 2000 - 2003, it is correct.
What is the problem? Is it a bug of Excel 2007?

Best regards,
Liu Jianzhong


Sub test()
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

'Get Excel sheet
On Error GoTo llExcelSheetErr
Set xlWorkBook = ActiveWorkbook
Set xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
xlWorkSheet.Range("5:20").Insert xlShiftDown
' xlWorkSheet.Range("1:4").AutoFill xlWorkSheet.Range("1:20"),
xlFillCopy
xlWorkSheet.Range("1:4").Copy xlWorkSheet.Range("5:20")
Exit Sub

llExcelSheetErr:
Call MsgBox(Err.Description, vbExclamation, "VBA Test")
End Sub