VBA Macro to Copy a Named Range as a Picture from Target Excel File
Sub Copy_Named_Range_As_Picture()
Dim WS As Worksheet
Dim Tgt_File As Object
Dim MyTab as Object
Dim CopyRange as Object
Dim Src_Path As String
Dim Src_File As String
Dim RngName As String
Dim Tgt_Sht As String
Dim This_WB As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set This_WB = ThisWorkbook
Set MyTab = This_WB.Sheets("Graphs")
Src_Path = This_WB.Sheets("CPanel").Range("A10").Value & "\"
Src_File_Name = "Regional_Sales*.xls*"
'Opening the Target file from the specified source Path
Src_File = Dir(Src_Path & Src_File_Name)
Set Tgt_File = Workbooks.Open(Src_Path & Src_File, UpdateLinks:=False, ReadOnly:=True, Editable:=True)
Application.Calculation = xlCalculationAutomatic
'The Named Range which we want to Copy from Target Excel file.
RngName = "Sales_Chart"
'Defining the Target Named Range to Copy
Set CopyRange = Tgt_File.Names(RngName).RefersToRange
'Getting Target Sheet name where the Named Range exists
Tgt_Sht = Tgt_File.Names(RngName).RefersToRange.Parent.Name
Tgt_File.Sheets(Tgt_Sht).Activate
CopyRange.Select
Selection.Copy
'Activating the Destination sheet and Pasting the copied Range as a Picture
MyTab.Activate
ActiveSheet.Range("E5").Select
ActiveSheet.Pictures.Paste.Select
Tgt_File.Activate
Tgt_File.Close
Set CopyRange=Nothing
Set This_WB=Nothing
Set MyTab=Nothing
EndSub
Sub Copy_Named_Range_As_Picture()
Dim WS As Worksheet
Dim Tgt_File As Object
Dim MyTab as Object
Dim CopyRange as Object
Dim Src_Path As String
Dim Src_File As String
Dim RngName As String
Dim Tgt_Sht As String
Dim This_WB As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set This_WB = ThisWorkbook
Set MyTab = This_WB.Sheets("Graphs")
Src_Path = This_WB.Sheets("CPanel").Range("A10").Value & "\"
Src_File_Name = "Regional_Sales*.xls*"
'Opening the Target file from the specified source Path
Src_File = Dir(Src_Path & Src_File_Name)
Set Tgt_File = Workbooks.Open(Src_Path & Src_File, UpdateLinks:=False, ReadOnly:=True, Editable:=True)
Application.Calculation = xlCalculationAutomatic
'The Named Range which we want to Copy from Target Excel file.
RngName = "Sales_Chart"
'Defining the Target Named Range to Copy
Set CopyRange = Tgt_File.Names(RngName).RefersToRange
'Getting Target Sheet name where the Named Range exists
Tgt_Sht = Tgt_File.Names(RngName).RefersToRange.Parent.Name
Tgt_File.Sheets(Tgt_Sht).Activate
CopyRange.Select
Selection.Copy
'Activating the Destination sheet and Pasting the copied Range as a Picture
MyTab.Activate
ActiveSheet.Range("E5").Select
ActiveSheet.Pictures.Paste.Select
Tgt_File.Activate
Tgt_File.Close
Set CopyRange=Nothing
Set This_WB=Nothing
Set MyTab=Nothing
EndSub
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.