Excel VBA code to copy several ranges to specific sheet in another workbook
I found some code here that copies my data to another sheet which works fine. I am trying to extend it so that it copies to another Workbook and Specific Worksheet.
Sub Put_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
With Sheets("GasMe18-19")
lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).row + 1
End With
arr1 = Array("A", "G", "H", "L")
arr2 = Array("Q", "R", "S", "T")
For i = LBound(arr1) To UBound(arr1)
With Sheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
How can this be modified to put the data ranges into another Workbook? I put Sheet1 in the home Workbook and everything seems fine and I like the existing code base. The originating Workbook.Worksheet has 4 columns of data that I add to every few days. I then copy and paste into another Workbook2.WorkSheet2 for plotting. I prefer to copy everything from Row 4 to the last row of data for the 4 columns.
excel vba excel-vba worksheet
asked Nov 22 '18 at 5:48
user50506user50506
1204
add a comment |
I found some code here that copies my data to another sheet which works fine. I am trying to extend it so that it copies to another Workbook and Specific Worksheet.
Sub Put_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
With Sheets("GasMe18-19")
lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).row + 1
End With
arr1 = Array("A", "G", "H", "L")
arr2 = Array("Q", "R", "S", "T")
For i = LBound(arr1) To UBound(arr1)
With Sheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
How can this be modified to put the data ranges into another Workbook? I put Sheet1 in the home Workbook and everything seems fine and I like the existing code base. The originating Workbook.Worksheet has 4 columns of data that I add to every few days. I then copy and paste into another Workbook2.WorkSheet2 for plotting. I prefer to copy everything from Row 4 to the last row of data for the 4 columns.
excel vba excel-vba worksheet
asked Nov 22 '18 at 5:48
user50506user50506
1204
add a comment |
I found some code here that copies my data to another sheet which works fine. I am trying to extend it so that it copies to another Workbook and Specific Worksheet.
Sub Put_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
With Sheets("GasMe18-19")
lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).row + 1
End With
arr1 = Array("A", "G", "H", "L")
arr2 = Array("Q", "R", "S", "T")
For i = LBound(arr1) To UBound(arr1)
With Sheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
How can this be modified to put the data ranges into another Workbook? I put Sheet1 in the home Workbook and everything seems fine and I like the existing code base. The originating Workbook.Worksheet has 4 columns of data that I add to every few days. I then copy and paste into another Workbook2.WorkSheet2 for plotting. I prefer to copy everything from Row 4 to the last row of data for the 4 columns.
excel vba excel-vba worksheet
asked Nov 22 '18 at 5:48
user50506user50506
1204
I found some code here that copies my data to another sheet which works fine. I am trying to extend it so that it copies to another Workbook and Specific Worksheet.
Sub Put_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
With Sheets("GasMe18-19")
lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).row + 1
End With
arr1 = Array("A", "G", "H", "L")
arr2 = Array("Q", "R", "S", "T")
For i = LBound(arr1) To UBound(arr1)
With Sheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
How can this be modified to put the data ranges into another Workbook? I put Sheet1 in the home Workbook and everything seems fine and I like the existing code base. The originating Workbook.Worksheet has 4 columns of data that I add to every few days. I then copy and paste into another Workbook2.WorkSheet2 for plotting. I prefer to copy everything from Row 4 to the last row of data for the 4 columns.
excel vba excel-vba worksheet
excel vba excel-vba worksheet
asked Nov 22 '18 at 5:48
user50506user50506
1204
asked Nov 22 '18 at 5:48
user50506user50506
1204
asked Nov 22 '18 at 5:48
user50506user50506
1204
asked Nov 22 '18 at 5:48
user50506user50506
1204
asked Nov 22 '18 at 5:48
user50506user50506
1204
1204
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You need to qualify your range with the workbook in question
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")
...
For i = LBound(arr1) To UBound(arr1)
With wbSource.Worksheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:
Set wbTarget = Workbooks("TheTargetWBName")
If the target workbook is not open, then you would simply change to
Set wbTarget = Workbooks.Open("TheTargetWBName")
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
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%2f53424604%2fexcel-vba-code-to-copy-several-ranges-to-specific-sheet-in-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
You need to qualify your range with the workbook in question
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")
...
For i = LBound(arr1) To UBound(arr1)
With wbSource.Worksheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:
Set wbTarget = Workbooks("TheTargetWBName")
If the target workbook is not open, then you would simply change to
Set wbTarget = Workbooks.Open("TheTargetWBName")
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
add a comment |
You need to qualify your range with the workbook in question
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")
...
For i = LBound(arr1) To UBound(arr1)
With wbSource.Worksheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:
Set wbTarget = Workbooks("TheTargetWBName")
If the target workbook is not open, then you would simply change to
Set wbTarget = Workbooks.Open("TheTargetWBName")
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
add a comment |
You need to qualify your range with the workbook in question
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")
...
For i = LBound(arr1) To UBound(arr1)
With wbSource.Worksheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:
Set wbTarget = Workbooks("TheTargetWBName")
If the target workbook is not open, then you would simply change to
Set wbTarget = Workbooks.Open("TheTargetWBName")
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
You need to qualify your range with the workbook in question
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")
...
For i = LBound(arr1) To UBound(arr1)
With wbSource.Worksheets("GasMe18-19")
lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
End With
Next
Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:
Set wbTarget = Workbooks("TheTargetWBName")
If the target workbook is not open, then you would simply change to
Set wbTarget = Workbooks.Open("TheTargetWBName")
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
answered Nov 22 '18 at 5:57
K.DᴀᴠɪsK.Dᴀᴠɪs
7,114112339
7,114112339
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
add a comment |
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
Works great but how would I check if the target Workbook was open in my instance of Excel and use the appropriate wbTarget ?
– user50506
Nov 29 '18 at 8:42
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.
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%2f53424604%2fexcel-vba-code-to-copy-several-ranges-to-specific-sheet-in-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
