Friday, July 10, 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


--------------------------------------------------------------------------------------------------------
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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog