none
Is there a way to conditionally pull a named range into another workbook? RRS feed

  • Question

  • Good morning everybody.

    Is there a way to conditionally pull a named range from another workbook into one you are currently using right now. Like if I would put the name of the workbook (lets call it Bla) in cell A2 and the name of a range in Bla (lets call it Chart) in cell A3. (This named range is from C3:F15), So that it would automatically put that data into a sheet in the second workbook? And if so could I change the contents in cell A3 for another named range to appear instead?

    Thank you.

    -John

    Wednesday, April 29, 2015 2:24 PM

Answers

  • Hi John,

    >>Is there a way to conditionally pull a named range from another workbook into one you are currently using right now.

    It is possible. You could copy a range from a workbook to another workbook. A simple demo as below, you could modify it as your own requirement.

    Sub copy()
        Dim x As Workbook
        Dim y As Workbook
        Set y = Application.ActiveWorkbook
        Set x = Workbooks.Open(ActiveSheet.Range("A2").Value) 'set the file path in the A2 cell
        x.Sheets("Sheet1").Range("Chart").copy 'Chart is the range name which you want to copy
        y.Sheets("Sheet1").Range("A5").PasteSpecial
    End Sub

    >> And if so could I change the contents in cell A3 for another named range to appear instead?

    Yes, you could change the value of the cell by "y.Sheets("Sheet1").Range("A3").Value = "Chartest"". Keep in mind, you need to make sure the range named "Chart" exist in the workbook.

    You could refer the link below for more information about Range
    # Range Object (Excel)
    https://msdn.microsoft.com/en-us/library/office/ff838238.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 30, 2015 7:40 AM

All replies

  • Hi John,

    >>Is there a way to conditionally pull a named range from another workbook into one you are currently using right now.

    It is possible. You could copy a range from a workbook to another workbook. A simple demo as below, you could modify it as your own requirement.

    Sub copy()
        Dim x As Workbook
        Dim y As Workbook
        Set y = Application.ActiveWorkbook
        Set x = Workbooks.Open(ActiveSheet.Range("A2").Value) 'set the file path in the A2 cell
        x.Sheets("Sheet1").Range("Chart").copy 'Chart is the range name which you want to copy
        y.Sheets("Sheet1").Range("A5").PasteSpecial
    End Sub

    >> And if so could I change the contents in cell A3 for another named range to appear instead?

    Yes, you could change the value of the cell by "y.Sheets("Sheet1").Range("A3").Value = "Chartest"". Keep in mind, you need to make sure the range named "Chart" exist in the workbook.

    You could refer the link below for more information about Range
    # Range Object (Excel)
    https://msdn.microsoft.com/en-us/library/office/ff838238.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 30, 2015 7:40 AM
  • Thank you very much!

    All I need to know now is how do I change the macro so that I dont have to close the workbook every time I want to copy and paste the range.

    Thank you. 

    -John

    Tuesday, May 12, 2015 9:25 PM
  • Hi Jkicker,

    If you have opened the workbook, you could use the code below:

    Sub copy()
        Dim x As Workbook
        Dim y As Workbook
        Set y = Application.ActiveWorkbook
        Set x = Application.Workbooks("CopyRangeSouce.xlsx") 
        x.Sheets("Sheet1").Range("Chart").copy 'Chart is the range name which you want to copy
        y.Sheets("Sheet1").Range("A5").PasteSpecial
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 13, 2015 9:35 AM
  • Yes this worked just like I wanted it.

    But now I'm going to use this macro to pull different named ranges from different Sheets from the workbook called BLA. How would the program change if I instead of

        x.Sheets("Sheet1").Range("Chart").copy 'Chart is the range name which you want to copy

    It would search Cell C4 for a the sheet name and cell D4 for the Named Range

    Thank you.

    -John

    Wednesday, May 13, 2015 5:14 PM
  • Hi John,

    I am glad you original issue has been resolved, for this new issue, I will recommend you post a new thread, and then there would be more community members to help you.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 14, 2015 1:54 AM