none
A little help with a small macro RRS feed

  • Question

  • Good day everybody,

    I want to use this macro

    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

    It works perfectly but I just need the row thats in bold to change so that it searches the sheet name in cell C4 of the current workbook, and the name of the range in cell D4. Thank you alot

    Thank you.

    -John

    Friday, May 15, 2015 6:00 PM

Answers

  • Does this do what you want?

    Sub copy()
        Dim x As Workbook
        Dim y As Workbook
        Dim s As String
        Dim r As String
        Set y = ActiveWorkbook
        Set x = Workbooks("CopyRangeSouce.xlsx")
        s = ActiveSheet.Range("C4").Value
        r = ActiveSheet.Range("D4").Value
        x.Sheets(s).Range(r).copy
        y.Sheets("Sheet1").Range("A5").PasteSpecial
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JKicker Monday, May 18, 2015 8:15 PM
    Friday, May 15, 2015 6:20 PM

All replies

  • Does this do what you want?

    Sub copy()
        Dim x As Workbook
        Dim y As Workbook
        Dim s As String
        Dim r As String
        Set y = ActiveWorkbook
        Set x = Workbooks("CopyRangeSouce.xlsx")
        s = ActiveSheet.Range("C4").Value
        r = ActiveSheet.Range("D4").Value
        x.Sheets(s).Range(r).copy
        y.Sheets("Sheet1").Range("A5").PasteSpecial
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JKicker Monday, May 18, 2015 8:15 PM
    Friday, May 15, 2015 6:20 PM
  • Yes, thank you very much, this is exactly what I was looking for.

    Just one more thing, can it be edited that it still pulls the name ranges, even if the files were not opened in the same instance? Becouse I have two screens and I would like to see both files at the same time.

    Thank you.

    -John

    Monday, May 18, 2015 8:18 PM
  • No, Excel code works within one instance of Excel only.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 20, 2015 2:30 PM