Macro to copy an image (or picture) from one workbook to a new sheetin another workbook

[Ruchir]

Hello Friends,

This group has been of great help for all starters like me. Thanks a
lot!!

It would be great if somebody could please help me with one of my
queries.

I have a workbook (say, old.xls) which contains a hidden sheet with
name "logo". This contains a sheet with an image (in form of company
logo) along with many other sheets. I have already written a macro
that copies a particular sheet from this workbook and generates a new
workbook (say, new.xls) with the same sheet name in the same directory
as old.xls. Now, I need to copy the image or logo in the hidden sheet
from old.xls to a new sheet in new.xls. The problem is, both the
workbook names 'old.xls' and 'new.xls' are dynamic in nature i.e. they
change each month so I cant hard code the macro with these names.

I am really stuck with this and it's impacting my work a great deal. I
would be highly obliged if somebody from the group could please help
me with this.

Thanks, and I look forward to hearing from somebody soon.

Best regards,
Ruchir

 

[NoodNutt]

Credit: Kudo's to Ron Debruin for most of the code here which is bits &
pieces from his website.

G'day Ruchir

I was thinking of something along the lines of, whilst you have the current
sheet your working on open you could run this code (Modified with your
specific dir & filenames), which will save your file with a month/year
extension on it. So once the sheet is saved with a different Name + ext you
shouldn't have a filename conflict

You could then add your code to the end of this to copy your hidden sheet to
the new workbook

This code is tested & works well.

Sub Save_Me()

Dim MyFileExtStr As String
Dim MyWB As Workbook
Dim MyFileDate As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set MyWB = ActiveWorkbook

With MyWB
If Val(Application.Version) < 12 Then
MyFileExtStr = ".xls": MyFileFormatNum = -4143
Else
If .HasVBProject Then
MyFileExtStr = ".xlsm": MyFileFormatNum = 52
Else
MyFileExtStr = ".xlsx": MyFileFormatNum = 51
End If
End If
End With

MyFileDate = Format(Now, "mmm-yyyy")

With MyWB
ChDir "E:\Work_Stuff\"
.SaveAs Filename:="E:\Work_Stuff\SalesFigures" & " For " &
MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum
On Error Resume Next
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

HTH
Mark.