open and modify a value from another workbook
up vote
0
down vote
favorite
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
add a comment |
up vote
0
down vote
favorite
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
2
I assume you forgot to add quotes herey.Sheets("Sheet1").Range(A5).Value = vals
. It should bey.Sheets("Sheet1").Range("A5").Value = vals
, right? And doesSheet1
exist 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 withvals = Sheet12.Range("A3:B3").Value
and you only copy the first value of it withy.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
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
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
excel vba excel-vba
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 herey.Sheets("Sheet1").Range(A5).Value = vals
. It should bey.Sheets("Sheet1").Range("A5").Value = vals
, right? And doesSheet1
exist 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 withvals = Sheet12.Range("A3:B3").Value
and you only copy the first value of it withy.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
add a comment |
2
I assume you forgot to add quotes herey.Sheets("Sheet1").Range(A5).Value = vals
. It should bey.Sheets("Sheet1").Range("A5").Value = vals
, right? And doesSheet1
exist 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 withvals = Sheet12.Range("A3:B3").Value
and you only copy the first value of it withy.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 Sheet1
exist 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 Sheet1
exist 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
add a comment |
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`
add a comment |
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`
add a comment |
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`
add a comment |
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`
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`
answered Nov 10 at 18:16
GMalc
832149
832149
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
2
I assume you forgot to add quotes here
y.Sheets("Sheet1").Range(A5).Value = vals
. It should bey.Sheets("Sheet1").Range("A5").Value = vals
, right? And doesSheet1
exist 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 withvals = Sheet12.Range("A3:B3").Value
and you only copy the first value of it withy.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