Friday, 10 July 2015

How to Copy a Named Range as a Picture from Target Excel File

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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts