I have searched everywhere but I can only copy images to a new workbook but I can't place them in the exact location as the original workbook with the same dimensions, size, and name.
I need to copy all images from a workbook to a specified new workbook in the exact same location on the worksheet and the same dimensions and names as the original file.
The Source Workbook is specified on Worksheets("Transfer Formatting").Range("B3"), the Source Worksheet Name is specified on Worksheets("Transfer Formatting").Range("B9")
The Destination Workbook is specified on Worksheets("Transfer Formatting").Range("E3"), the Source Worksheet Name is specified on Worksheets("Transfer Formatting").Range("E9")
This is what I have so far:
I need to copy all images from a workbook to a specified new workbook in the exact same location on the worksheet and the same dimensions and names as the original file.
The Source Workbook is specified on Worksheets("Transfer Formatting").Range("B3"), the Source Worksheet Name is specified on Worksheets("Transfer Formatting").Range("B9")
The Destination Workbook is specified on Worksheets("Transfer Formatting").Range("E3"), the Source Worksheet Name is specified on Worksheets("Transfer Formatting").Range("E9")
This is what I have so far:
VBA Code:
Public Sub CopyImage2()
Dim p As Picture, r As Range, hasComment As Boolean
Dim fst As Variant, sec As Variant
Dim shp1 As Shape, shp2 As Shape
Dim myImage As Shape
Dim cellLocation As Range
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
OriginWB = ThisWorkbook.Worksheets("Transfer Formatting").Range("B3").Value
OriginPath = ThisWorkbook.Worksheets("Transfer Formatting").Range("B6").Value
OriginWS = ThisWorkbook.Worksheets("Transfer Formatting").Range("B9").Value
DestWB = ThisWorkbook.Worksheets("Transfer Formatting").Range("E3").Value
DestPath = ThisWorkbook.Worksheets("Transfer Formatting").Range("E6").Value
DestWS = ThisWorkbook.Worksheets("Transfer Formatting").Range("E9").Value
Set SourceWB = Workbooks(OriginWB)
Set SourceWS = SourceWB.Worksheets(OriginWS)
Set DestWB = Workbooks(DestWB)
Set DestWS = DestWB.Worksheets(DestWS)
ImageCount = DestWS.Shapes.Count
For i = 1 To ImageCount
DestWS.Activate
DestWS.Unprotect Password = vbNullString
SourceWS.Shapes(i).Copy
DestWS.Range("A1").Select
Application.Wait (Now + TimeValue("0:00:01"))
DestWS.Paste
DestWS.Shapes(i).Name = SourceWS.Shapes(i).Name
SHCount = DestWS.Shapes.Count
If Not DestWS.shapeExists(SHCount) Then
If DestWS.shapeExists(SHCount) Then
DestWS.Shapes(SHCount).Name = SourceWS.Shapes(i).Name
ElseIf WSD.shapeExists("Picture " & SHCount) Then
DestWS.Shapes("Picture " & SHCount).Name = SourceWS.Shapes(i).Name
End If
End If
With SourceWB.Shapes(i)
DestWS.Shapes(SourceWS.Shapes(i).Name).Left = .Left
DestWS.Shapes(SourceWS.Shapes(i).Name).Top = .Top
DestWS.Shapes(SourceWS.Shapes(i).Name).Height = .Height
DestWS.Shapes(SourceWS.Shapes(i).Name).Width = .Width
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub