open and modify a value from another workbook











up vote
0
down vote

favorite
1












Sub abrirotroworkbook()

Dim y As Workbook ' a donde se va a pegar
Dim vals As Variant

Set y = Workbooks.Open("C:Pathfilename.xlsm")

'Now, copy what you want from x:

vals = Sheet12.Range("A3:B3").Value


'Now, paste to y worksheet:

y.Sheets("Sheet1").Range(A5).Value = vals


End Sub


When I run this code it saves the information of the book I have opened in the variable vals, then it opens the other workbook, but when it tries to paste in it, the following error appears:




run time error '9' subscript out of range




I don't know what the problem is.










share|improve this question




















  • 2




    I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
    – Storax
    Nov 10 at 16:06












  • Thank you so much. The problem was like you said that Sheet1 didn't exist.
    – Luis Claret
    Nov 11 at 16:08















up vote
0
down vote

favorite
1












Sub abrirotroworkbook()

Dim y As Workbook ' a donde se va a pegar
Dim vals As Variant

Set y = Workbooks.Open("C:Pathfilename.xlsm")

'Now, copy what you want from x:

vals = Sheet12.Range("A3:B3").Value


'Now, paste to y worksheet:

y.Sheets("Sheet1").Range(A5).Value = vals


End Sub


When I run this code it saves the information of the book I have opened in the variable vals, then it opens the other workbook, but when it tries to paste in it, the following error appears:




run time error '9' subscript out of range




I don't know what the problem is.










share|improve this question




















  • 2




    I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
    – Storax
    Nov 10 at 16:06












  • Thank you so much. The problem was like you said that Sheet1 didn't exist.
    – Luis Claret
    Nov 11 at 16:08













up vote
0
down vote

favorite
1









up vote
0
down vote

favorite
1






1





Sub abrirotroworkbook()

Dim y As Workbook ' a donde se va a pegar
Dim vals As Variant

Set y = Workbooks.Open("C:Pathfilename.xlsm")

'Now, copy what you want from x:

vals = Sheet12.Range("A3:B3").Value


'Now, paste to y worksheet:

y.Sheets("Sheet1").Range(A5).Value = vals


End Sub


When I run this code it saves the information of the book I have opened in the variable vals, then it opens the other workbook, but when it tries to paste in it, the following error appears:




run time error '9' subscript out of range




I don't know what the problem is.










share|improve this question















Sub abrirotroworkbook()

Dim y As Workbook ' a donde se va a pegar
Dim vals As Variant

Set y = Workbooks.Open("C:Pathfilename.xlsm")

'Now, copy what you want from x:

vals = Sheet12.Range("A3:B3").Value


'Now, paste to y worksheet:

y.Sheets("Sheet1").Range(A5).Value = vals


End Sub


When I run this code it saves the information of the book I have opened in the variable vals, then it opens the other workbook, but when it tries to paste in it, the following error appears:




run time error '9' subscript out of range




I don't know what the problem is.







excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 at 7:18









Pᴇʜ

18.7k42549




18.7k42549










asked Nov 10 at 15:34









Luis Claret

1




1








  • 2




    I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
    – Storax
    Nov 10 at 16:06












  • Thank you so much. The problem was like you said that Sheet1 didn't exist.
    – Luis Claret
    Nov 11 at 16:08














  • 2




    I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
    – Storax
    Nov 10 at 16:06












  • Thank you so much. The problem was like you said that Sheet1 didn't exist.
    – Luis Claret
    Nov 11 at 16:08








2




2




I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
– Storax
Nov 10 at 16:06






I assume you forgot to add quotes here y.Sheets("Sheet1").Range(A5).Value = vals. It should be y.Sheets("Sheet1").Range("A5").Value = vals, right? And does Sheet1exist in your workbook y you openend. That is usually the cause for runtime error 9 in such a case. Are you also aware that you create an array with vals = Sheet12.Range("A3:B3").Value and you only copy the first value of it with y.Sheets("Sheet1").Range(A5).Value = vals.
– Storax
Nov 10 at 16:06














Thank you so much. The problem was like you said that Sheet1 didn't exist.
– Luis Claret
Nov 11 at 16:08




Thank you so much. The problem was like you said that Sheet1 didn't exist.
– Luis Claret
Nov 11 at 16:08












1 Answer
1






active

oldest

votes

















up vote
0
down vote













As @Storax said, your variable vals is not opening a workbook but creating an array.
You don't need an array for just copying two cells. You would have to loop through the array. The code below is basic. If you are copying from ThisWorkbook(the workbook your code is in) it will work. If it is another workbook then you can create another variable to open it, and use the variable instead of ThisWorkbook.



Dim wb1 As Workbook 'the source workbook
Set wb1 = Workbooks.Open("C:Pathsourcefilename.xlsm") 'change name and path as required

Dim wb2 As Workbook 'the destination workbook
Set wb2 = Workbooks.Open("C:Pathdestinationfilename.xlsm") 'change name and path as required

wb2.Sheets("Sheet1").Range("A5:B5").Value = ThisWorkbook.Sheets("Sheet12").Range("A3:B3").Value 'replace ThisWorkbook with `wb1`





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240477%2fopen-and-modify-a-value-from-another-workbook%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    As @Storax said, your variable vals is not opening a workbook but creating an array.
    You don't need an array for just copying two cells. You would have to loop through the array. The code below is basic. If you are copying from ThisWorkbook(the workbook your code is in) it will work. If it is another workbook then you can create another variable to open it, and use the variable instead of ThisWorkbook.



    Dim wb1 As Workbook 'the source workbook
    Set wb1 = Workbooks.Open("C:Pathsourcefilename.xlsm") 'change name and path as required

    Dim wb2 As Workbook 'the destination workbook
    Set wb2 = Workbooks.Open("C:Pathdestinationfilename.xlsm") 'change name and path as required

    wb2.Sheets("Sheet1").Range("A5:B5").Value = ThisWorkbook.Sheets("Sheet12").Range("A3:B3").Value 'replace ThisWorkbook with `wb1`





    share|improve this answer

























      up vote
      0
      down vote













      As @Storax said, your variable vals is not opening a workbook but creating an array.
      You don't need an array for just copying two cells. You would have to loop through the array. The code below is basic. If you are copying from ThisWorkbook(the workbook your code is in) it will work. If it is another workbook then you can create another variable to open it, and use the variable instead of ThisWorkbook.



      Dim wb1 As Workbook 'the source workbook
      Set wb1 = Workbooks.Open("C:Pathsourcefilename.xlsm") 'change name and path as required

      Dim wb2 As Workbook 'the destination workbook
      Set wb2 = Workbooks.Open("C:Pathdestinationfilename.xlsm") 'change name and path as required

      wb2.Sheets("Sheet1").Range("A5:B5").Value = ThisWorkbook.Sheets("Sheet12").Range("A3:B3").Value 'replace ThisWorkbook with `wb1`





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        As @Storax said, your variable vals is not opening a workbook but creating an array.
        You don't need an array for just copying two cells. You would have to loop through the array. The code below is basic. If you are copying from ThisWorkbook(the workbook your code is in) it will work. If it is another workbook then you can create another variable to open it, and use the variable instead of ThisWorkbook.



        Dim wb1 As Workbook 'the source workbook
        Set wb1 = Workbooks.Open("C:Pathsourcefilename.xlsm") 'change name and path as required

        Dim wb2 As Workbook 'the destination workbook
        Set wb2 = Workbooks.Open("C:Pathdestinationfilename.xlsm") 'change name and path as required

        wb2.Sheets("Sheet1").Range("A5:B5").Value = ThisWorkbook.Sheets("Sheet12").Range("A3:B3").Value 'replace ThisWorkbook with `wb1`





        share|improve this answer












        As @Storax said, your variable vals is not opening a workbook but creating an array.
        You don't need an array for just copying two cells. You would have to loop through the array. The code below is basic. If you are copying from ThisWorkbook(the workbook your code is in) it will work. If it is another workbook then you can create another variable to open it, and use the variable instead of ThisWorkbook.



        Dim wb1 As Workbook 'the source workbook
        Set wb1 = Workbooks.Open("C:Pathsourcefilename.xlsm") 'change name and path as required

        Dim wb2 As Workbook 'the destination workbook
        Set wb2 = Workbooks.Open("C:Pathdestinationfilename.xlsm") 'change name and path as required

        wb2.Sheets("Sheet1").Range("A5:B5").Value = ThisWorkbook.Sheets("Sheet12").Range("A3:B3").Value 'replace ThisWorkbook with `wb1`






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 10 at 18:16









        GMalc

        832149




        832149






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240477%2fopen-and-modify-a-value-from-another-workbook%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Florida Star v. B. J. F.

            Error while running script in elastic search , gateway timeout

            Adding quotations to stringified JSON object values