Merge Multiple Worksheets into a Single Worksheet in the Same Workbook
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I currently have code for each sheet I want to move but I am wondering if there was a way to reduce this code.
This is what I currently use to move each sheet times 8 or so sheets:
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "ONI" Then
Set RNG1 = ONI.Range("A1:AK1").EntireColumn
Set RNG2 = All.Range("A1:AK1").EntireColumn
RNG2.Value = RNG1.Value
End If
Next
This is the code I use when I want to move a single column from all sheets to a single sheet. I can't figure out how to modify it to include more columns.
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MainSheet" Then
Set RNG1 = ws.Range("A1:A700")
Set RNG2 = Sheets ("MainSheet") _
.Cells(Rows.Count,"A").End(xlUp).Offset(1)
RNG2.Value = RNG1.Value
End If
Next
So basically is it possible to modify this code to include multiple columns?
excel vba excel-vba
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
add a comment |
I currently have code for each sheet I want to move but I am wondering if there was a way to reduce this code.
This is what I currently use to move each sheet times 8 or so sheets:
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "ONI" Then
Set RNG1 = ONI.Range("A1:AK1").EntireColumn
Set RNG2 = All.Range("A1:AK1").EntireColumn
RNG2.Value = RNG1.Value
End If
Next
This is the code I use when I want to move a single column from all sheets to a single sheet. I can't figure out how to modify it to include more columns.
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MainSheet" Then
Set RNG1 = ws.Range("A1:A700")
Set RNG2 = Sheets ("MainSheet") _
.Cells(Rows.Count,"A").End(xlUp).Offset(1)
RNG2.Value = RNG1.Value
End If
Next
So basically is it possible to modify this code to include multiple columns?
excel vba excel-vba
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
What isAllin your first code snippet?
– dwirony
Nov 26 '18 at 22:30
Do you really wantA1:A700every time or do you just wantA1down to the last row?
– urdearboy
Nov 26 '18 at 22:32
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35
add a comment |
I currently have code for each sheet I want to move but I am wondering if there was a way to reduce this code.
This is what I currently use to move each sheet times 8 or so sheets:
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "ONI" Then
Set RNG1 = ONI.Range("A1:AK1").EntireColumn
Set RNG2 = All.Range("A1:AK1").EntireColumn
RNG2.Value = RNG1.Value
End If
Next
This is the code I use when I want to move a single column from all sheets to a single sheet. I can't figure out how to modify it to include more columns.
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MainSheet" Then
Set RNG1 = ws.Range("A1:A700")
Set RNG2 = Sheets ("MainSheet") _
.Cells(Rows.Count,"A").End(xlUp).Offset(1)
RNG2.Value = RNG1.Value
End If
Next
So basically is it possible to modify this code to include multiple columns?
excel vba excel-vba
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
I currently have code for each sheet I want to move but I am wondering if there was a way to reduce this code.
This is what I currently use to move each sheet times 8 or so sheets:
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "ONI" Then
Set RNG1 = ONI.Range("A1:AK1").EntireColumn
Set RNG2 = All.Range("A1:AK1").EntireColumn
RNG2.Value = RNG1.Value
End If
Next
This is the code I use when I want to move a single column from all sheets to a single sheet. I can't figure out how to modify it to include more columns.
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MainSheet" Then
Set RNG1 = ws.Range("A1:A700")
Set RNG2 = Sheets ("MainSheet") _
.Cells(Rows.Count,"A").End(xlUp).Offset(1)
RNG2.Value = RNG1.Value
End If
Next
So basically is it possible to modify this code to include multiple columns?
excel vba excel-vba
excel vba excel-vba
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
edited Nov 27 '18 at 8:14
Pᴇʜ
25.2k63052
25.2k63052
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
asked Nov 26 '18 at 22:22
Bruce JenksBruce Jenks
328
328
What isAllin your first code snippet?
– dwirony
Nov 26 '18 at 22:30
Do you really wantA1:A700every time or do you just wantA1down to the last row?
– urdearboy
Nov 26 '18 at 22:32
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35
add a comment |
What isAllin your first code snippet?
– dwirony
Nov 26 '18 at 22:30
Do you really wantA1:A700every time or do you just wantA1down to the last row?
– urdearboy
Nov 26 '18 at 22:32
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35
What is
All in your first code snippet?– dwirony
Nov 26 '18 at 22:30
What is
All in your first code snippet?– dwirony
Nov 26 '18 at 22:30
Do you really want
A1:A700 every time or do you just want A1 down to the last row?– urdearboy
Nov 26 '18 at 22:32
Do you really want
A1:A700 every time or do you just want A1 down to the last row?– urdearboy
Nov 26 '18 at 22:32
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35
add a comment |
2 Answers
2
active
oldest
votes
Kudos for going for the value transfer instead of copy/paste. You just need to resize your Rng2 to match the size of Rng1.
I also modified this to work with dynamic row counts. If you need to copy a static range for each sheet, you can get rid of the LR bits and hard code the range. You need to keep nLR as this determines the next available row on your main sheet.
Sub Test()
Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("MainSheet")
Dim ws As Worksheet, Rng1 As Range, Rng2 As Range
Dim LR As Long, nLR As Long '(LR = Last Row, nLR = New Last Row for Main Sheet)
For Each ws In Worksheets
If ws.Name <> ms.Name Then
'Determine Relavent Ranges (last rows)
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
nLR = ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).Row
'Set the ranges
Set Rng1 = ws.Range("A1:L" & LR)
Set Rng2 = ms.Range("A" & nLR).Resize(Rng1.Rows.Count, Rng1.Columns.Count)
'Value Transfer
Rng2.Value = Rng1.Value
End If
Next ws
End Sub
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
add a comment |
Think you need a nested loop here, long time since i wrote vba so i give pseudo code, hope this help you on the way.
for each ws
dim rang as Range
for Each rnge In Range("A1:H1").Columns
do something
next
next
answered Nov 26 '18 at 22:34
JoaJoa
464
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%2f53490015%2fmerge-multiple-worksheets-into-a-single-worksheet-in-the-same-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Kudos for going for the value transfer instead of copy/paste. You just need to resize your Rng2 to match the size of Rng1.
I also modified this to work with dynamic row counts. If you need to copy a static range for each sheet, you can get rid of the LR bits and hard code the range. You need to keep nLR as this determines the next available row on your main sheet.
Sub Test()
Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("MainSheet")
Dim ws As Worksheet, Rng1 As Range, Rng2 As Range
Dim LR As Long, nLR As Long '(LR = Last Row, nLR = New Last Row for Main Sheet)
For Each ws In Worksheets
If ws.Name <> ms.Name Then
'Determine Relavent Ranges (last rows)
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
nLR = ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).Row
'Set the ranges
Set Rng1 = ws.Range("A1:L" & LR)
Set Rng2 = ms.Range("A" & nLR).Resize(Rng1.Rows.Count, Rng1.Columns.Count)
'Value Transfer
Rng2.Value = Rng1.Value
End If
Next ws
End Sub
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
add a comment |
Kudos for going for the value transfer instead of copy/paste. You just need to resize your Rng2 to match the size of Rng1.
I also modified this to work with dynamic row counts. If you need to copy a static range for each sheet, you can get rid of the LR bits and hard code the range. You need to keep nLR as this determines the next available row on your main sheet.
Sub Test()
Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("MainSheet")
Dim ws As Worksheet, Rng1 As Range, Rng2 As Range
Dim LR As Long, nLR As Long '(LR = Last Row, nLR = New Last Row for Main Sheet)
For Each ws In Worksheets
If ws.Name <> ms.Name Then
'Determine Relavent Ranges (last rows)
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
nLR = ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).Row
'Set the ranges
Set Rng1 = ws.Range("A1:L" & LR)
Set Rng2 = ms.Range("A" & nLR).Resize(Rng1.Rows.Count, Rng1.Columns.Count)
'Value Transfer
Rng2.Value = Rng1.Value
End If
Next ws
End Sub
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
add a comment |
Kudos for going for the value transfer instead of copy/paste. You just need to resize your Rng2 to match the size of Rng1.
I also modified this to work with dynamic row counts. If you need to copy a static range for each sheet, you can get rid of the LR bits and hard code the range. You need to keep nLR as this determines the next available row on your main sheet.
Sub Test()
Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("MainSheet")
Dim ws As Worksheet, Rng1 As Range, Rng2 As Range
Dim LR As Long, nLR As Long '(LR = Last Row, nLR = New Last Row for Main Sheet)
For Each ws In Worksheets
If ws.Name <> ms.Name Then
'Determine Relavent Ranges (last rows)
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
nLR = ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).Row
'Set the ranges
Set Rng1 = ws.Range("A1:L" & LR)
Set Rng2 = ms.Range("A" & nLR).Resize(Rng1.Rows.Count, Rng1.Columns.Count)
'Value Transfer
Rng2.Value = Rng1.Value
End If
Next ws
End Sub
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
Kudos for going for the value transfer instead of copy/paste. You just need to resize your Rng2 to match the size of Rng1.
I also modified this to work with dynamic row counts. If you need to copy a static range for each sheet, you can get rid of the LR bits and hard code the range. You need to keep nLR as this determines the next available row on your main sheet.
Sub Test()
Dim ms As Worksheet: Set ms = ThisWorkbook.Sheets("MainSheet")
Dim ws As Worksheet, Rng1 As Range, Rng2 As Range
Dim LR As Long, nLR As Long '(LR = Last Row, nLR = New Last Row for Main Sheet)
For Each ws In Worksheets
If ws.Name <> ms.Name Then
'Determine Relavent Ranges (last rows)
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
nLR = ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).Row
'Set the ranges
Set Rng1 = ws.Range("A1:L" & LR)
Set Rng2 = ms.Range("A" & nLR).Resize(Rng1.Rows.Count, Rng1.Columns.Count)
'Value Transfer
Rng2.Value = Rng1.Value
End If
Next ws
End Sub
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
answered Nov 26 '18 at 22:35
urdearboyurdearboy
8,0173731
8,0173731
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
add a comment |
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
Now I see it! Thanks for the help. You are right, I have been trying to move away from copy and paste and it has been so much better in terms of speed.
– Bruce Jenks
Nov 26 '18 at 22:50
add a comment |
Think you need a nested loop here, long time since i wrote vba so i give pseudo code, hope this help you on the way.
for each ws
dim rang as Range
for Each rnge In Range("A1:H1").Columns
do something
next
next
answered Nov 26 '18 at 22:34
JoaJoa
464
add a comment |
Think you need a nested loop here, long time since i wrote vba so i give pseudo code, hope this help you on the way.
for each ws
dim rang as Range
for Each rnge In Range("A1:H1").Columns
do something
next
next
answered Nov 26 '18 at 22:34
JoaJoa
464
add a comment |
Think you need a nested loop here, long time since i wrote vba so i give pseudo code, hope this help you on the way.
for each ws
dim rang as Range
for Each rnge In Range("A1:H1").Columns
do something
next
next
answered Nov 26 '18 at 22:34
JoaJoa
464
Think you need a nested loop here, long time since i wrote vba so i give pseudo code, hope this help you on the way.
for each ws
dim rang as Range
for Each rnge In Range("A1:H1").Columns
do something
next
next
answered Nov 26 '18 at 22:34
JoaJoa
464
answered Nov 26 '18 at 22:34
JoaJoa
464
answered Nov 26 '18 at 22:34
JoaJoa
464
answered Nov 26 '18 at 22:34
JoaJoa
464
464
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.
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%2f53490015%2fmerge-multiple-worksheets-into-a-single-worksheet-in-the-same-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
What is
Allin your first code snippet?– dwirony
Nov 26 '18 at 22:30
Do you really want
A1:A700every time or do you just wantA1down to the last row?– urdearboy
Nov 26 '18 at 22:32
Sorry I should have been more clear. I just want say "A1:L300" from all sheets to "A1:L300" to the MainSheet.
– Bruce Jenks
Nov 26 '18 at 22:35