Mixing
Excel VBA move values from one sheet to another
I need a suggestion. I have ~50 tables which are pasted one after another in one Excel sheet and I need to copy data from particular cell and paste to another workbook to another cell.
Now I'm using VBA to achieve this:
Assign value from cell to "number" number = data.Cells(500, "E").Value and then assign "number" to other cell result.Cells(3, "D") = number.
I need to do it with every table and now I'm just use the row number and column letter to determine the value. Here is an example how my tables looks like:
And my VBA code:
number_e = data.Cells(80, "E").Value
result.Cells(8, "D") = number_e
number_j = data.Cells(80, "L").Value
result.Cells(9, "D") = number_j
number_n = data.Cells(80, "P").Value
result.Cells(10, "D") = number_n
number_e = data.Cells(500, "E").Value
result.Cells(3, "D") = number_e
number_j = data.Cells(500, "J").Value
result.Cells(4, "D") = number_j
number_n = data.Cells(500, "N").Value
result.Cells(5, "D") = number_n
<...>
number_e = data.Cells(4385, "E").Value
result.Cells(242, "D") = number_e
number_j = data.Cells(4385, "L").Value
result.Cells(243, "D") = number_j
number_n = data.Cells(4385, "P").Value
result.Cells(244, "D") = number_n
In some tables the desired values are in different columns.
I believe there is more rational way to do this... Also I'm using values from one table one row but need to use more values from other rows and I don't want to go through writing every row number again...
Maybe I should convert data to tables and use references but I'm not sure if that can help.
Thank you for any suggestions.
excel vba excel-vba
asked Nov 19 '18 at 12:24
Katia
1910
add a comment |
I need a suggestion. I have ~50 tables which are pasted one after another in one Excel sheet and I need to copy data from particular cell and paste to another workbook to another cell.
Now I'm using VBA to achieve this:
Assign value from cell to "number" number = data.Cells(500, "E").Value and then assign "number" to other cell result.Cells(3, "D") = number.
I need to do it with every table and now I'm just use the row number and column letter to determine the value. Here is an example how my tables looks like:
And my VBA code:
number_e = data.Cells(80, "E").Value
result.Cells(8, "D") = number_e
number_j = data.Cells(80, "L").Value
result.Cells(9, "D") = number_j
number_n = data.Cells(80, "P").Value
result.Cells(10, "D") = number_n
number_e = data.Cells(500, "E").Value
result.Cells(3, "D") = number_e
number_j = data.Cells(500, "J").Value
result.Cells(4, "D") = number_j
number_n = data.Cells(500, "N").Value
result.Cells(5, "D") = number_n
<...>
number_e = data.Cells(4385, "E").Value
result.Cells(242, "D") = number_e
number_j = data.Cells(4385, "L").Value
result.Cells(243, "D") = number_j
number_n = data.Cells(4385, "P").Value
result.Cells(244, "D") = number_n
In some tables the desired values are in different columns.
I believe there is more rational way to do this... Also I'm using values from one table one row but need to use more values from other rows and I don't want to go through writing every row number again...
Maybe I should convert data to tables and use references but I'm not sure if that can help.
Thank you for any suggestions.
excel vba excel-vba
asked Nov 19 '18 at 12:24
Katia
1910
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12
add a comment |
I need a suggestion. I have ~50 tables which are pasted one after another in one Excel sheet and I need to copy data from particular cell and paste to another workbook to another cell.
Now I'm using VBA to achieve this:
Assign value from cell to "number" number = data.Cells(500, "E").Value and then assign "number" to other cell result.Cells(3, "D") = number.
I need to do it with every table and now I'm just use the row number and column letter to determine the value. Here is an example how my tables looks like:
And my VBA code:
number_e = data.Cells(80, "E").Value
result.Cells(8, "D") = number_e
number_j = data.Cells(80, "L").Value
result.Cells(9, "D") = number_j
number_n = data.Cells(80, "P").Value
result.Cells(10, "D") = number_n
number_e = data.Cells(500, "E").Value
result.Cells(3, "D") = number_e
number_j = data.Cells(500, "J").Value
result.Cells(4, "D") = number_j
number_n = data.Cells(500, "N").Value
result.Cells(5, "D") = number_n
<...>
number_e = data.Cells(4385, "E").Value
result.Cells(242, "D") = number_e
number_j = data.Cells(4385, "L").Value
result.Cells(243, "D") = number_j
number_n = data.Cells(4385, "P").Value
result.Cells(244, "D") = number_n
In some tables the desired values are in different columns.
I believe there is more rational way to do this... Also I'm using values from one table one row but need to use more values from other rows and I don't want to go through writing every row number again...
Maybe I should convert data to tables and use references but I'm not sure if that can help.
Thank you for any suggestions.
excel vba excel-vba
asked Nov 19 '18 at 12:24
Katia
1910
I need a suggestion. I have ~50 tables which are pasted one after another in one Excel sheet and I need to copy data from particular cell and paste to another workbook to another cell.
Now I'm using VBA to achieve this:
Assign value from cell to "number" number = data.Cells(500, "E").Value and then assign "number" to other cell result.Cells(3, "D") = number.
I need to do it with every table and now I'm just use the row number and column letter to determine the value. Here is an example how my tables looks like:
And my VBA code:
number_e = data.Cells(80, "E").Value
result.Cells(8, "D") = number_e
number_j = data.Cells(80, "L").Value
result.Cells(9, "D") = number_j
number_n = data.Cells(80, "P").Value
result.Cells(10, "D") = number_n
number_e = data.Cells(500, "E").Value
result.Cells(3, "D") = number_e
number_j = data.Cells(500, "J").Value
result.Cells(4, "D") = number_j
number_n = data.Cells(500, "N").Value
result.Cells(5, "D") = number_n
<...>
number_e = data.Cells(4385, "E").Value
result.Cells(242, "D") = number_e
number_j = data.Cells(4385, "L").Value
result.Cells(243, "D") = number_j
number_n = data.Cells(4385, "P").Value
result.Cells(244, "D") = number_n
In some tables the desired values are in different columns.
I believe there is more rational way to do this... Also I'm using values from one table one row but need to use more values from other rows and I don't want to go through writing every row number again...
Maybe I should convert data to tables and use references but I'm not sure if that can help.
Thank you for any suggestions.
excel vba excel-vba
excel vba excel-vba
asked Nov 19 '18 at 12:24
Katia
1910
asked Nov 19 '18 at 12:24
Katia
1910
asked Nov 19 '18 at 12:24
Katia
1910
asked Nov 19 '18 at 12:24
Katia
1910
asked Nov 19 '18 at 12:24
Katia
1910
1910
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12
add a comment |
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12
add a comment |
1 Answer
1
active
oldest
votes
' Checking number of records
rCount = data.Cells(.Rows.Count, 1).Row
' Copying the data
for i=1 to rCount
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
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%2f53374592%2fexcel-vba-move-values-from-one-sheet-to-another%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
' Checking number of records
rCount = data.Cells(.Rows.Count, 1).Row
' Copying the data
for i=1 to rCount
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
add a comment |
' Checking number of records
rCount = data.Cells(.Rows.Count, 1).Row
' Copying the data
for i=1 to rCount
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
add a comment |
' Checking number of records
rCount = data.Cells(.Rows.Count, 1).Row
' Copying the data
for i=1 to rCount
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
' Checking number of records
rCount = data.Cells(.Rows.Count, 1).Row
' Copying the data
for i=1 to rCount
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
answered Nov 19 '18 at 14:12
Rahul Chawla
550412
550412
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53374592%2fexcel-vba-move-values-from-one-sheet-to-another%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
Please provide more detail so someone can help you. What is your rational for choosing the row, column, and data that you are trying to copy to another worksheet. Also why you are pasting the data into rows 8,9,10, then into 3,4,5; it doesn't seem to follow a logical flow.
– GMalc
Nov 19 '18 at 14:12