merge multiple worksheets into one worksheet
0
I have trouble in this one...
I want to merge multiple worksheets into one. but everytime I run this code it overwrites some of the data. It should just copy and paste all the data into one worksheet named "Combined"
Dim ws As Worksheet
Set ws = Worksheets.Add(Before:=Worksheets(1))
ws.Name = "Combined"
Application.DisplayAlerts = False
Dim rng As Range, rng2 As Range
Set rng = Worksheets(2).Range("A1").CurrentRegion
rng.copy ws.Range("A1")
Dim i As Integer
Dim wsCopy As Worksheet, rngCopy As Range, rngDest As Range
For i = 3 To Worksheets.Count
Set wsCopy = Worksheets(i)
With wsCopy
Set rngCopy = .Range("A1").CurrentRegion
Set rngCopy = rngCopy.Offset(1, 0).Resize(rngCopy.Rows.Count - 1)
End With
With ws
Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
rngCopy.copy rngDest
Next i
' Delete original worksheets
For Each ws In Worksheets
If ws.Name <> "Combined" Then ws.Delete
Next ws
I have 3 worksheets. "Dept", "Saving", and "TimeDmnd" they all have the same header.
thank you.
microsoft-excel vba macros
asked Jan 30 at 6:20
geegee
344
add a comment |
0
I have trouble in this one...
I want to merge multiple worksheets into one. but everytime I run this code it overwrites some of the data. It should just copy and paste all the data into one worksheet named "Combined"
Dim ws As Worksheet
Set ws = Worksheets.Add(Before:=Worksheets(1))
ws.Name = "Combined"
Application.DisplayAlerts = False
Dim rng As Range, rng2 As Range
Set rng = Worksheets(2).Range("A1").CurrentRegion
rng.copy ws.Range("A1")
Dim i As Integer
Dim wsCopy As Worksheet, rngCopy As Range, rngDest As Range
For i = 3 To Worksheets.Count
Set wsCopy = Worksheets(i)
With wsCopy
Set rngCopy = .Range("A1").CurrentRegion
Set rngCopy = rngCopy.Offset(1, 0).Resize(rngCopy.Rows.Count - 1)
End With
With ws
Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
rngCopy.copy rngDest
Next i
' Delete original worksheets
For Each ws In Worksheets
If ws.Name <> "Combined" Then ws.Delete
Next ws
I have 3 worksheets. "Dept", "Saving", and "TimeDmnd" they all have the same header.
thank you.
microsoft-excel vba macros
asked Jan 30 at 6:20
geegee
344
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by addingrngCopy.Rows.Countto it) instead ofSet rngDeststatement.
– Akina
Jan 30 at 8:12
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59
add a comment |
0
0
0
I have trouble in this one...
I want to merge multiple worksheets into one. but everytime I run this code it overwrites some of the data. It should just copy and paste all the data into one worksheet named "Combined"
Dim ws As Worksheet
Set ws = Worksheets.Add(Before:=Worksheets(1))
ws.Name = "Combined"
Application.DisplayAlerts = False
Dim rng As Range, rng2 As Range
Set rng = Worksheets(2).Range("A1").CurrentRegion
rng.copy ws.Range("A1")
Dim i As Integer
Dim wsCopy As Worksheet, rngCopy As Range, rngDest As Range
For i = 3 To Worksheets.Count
Set wsCopy = Worksheets(i)
With wsCopy
Set rngCopy = .Range("A1").CurrentRegion
Set rngCopy = rngCopy.Offset(1, 0).Resize(rngCopy.Rows.Count - 1)
End With
With ws
Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
rngCopy.copy rngDest
Next i
' Delete original worksheets
For Each ws In Worksheets
If ws.Name <> "Combined" Then ws.Delete
Next ws
I have 3 worksheets. "Dept", "Saving", and "TimeDmnd" they all have the same header.
thank you.
microsoft-excel vba macros
asked Jan 30 at 6:20
geegee
344
I have trouble in this one...
I want to merge multiple worksheets into one. but everytime I run this code it overwrites some of the data. It should just copy and paste all the data into one worksheet named "Combined"
Dim ws As Worksheet
Set ws = Worksheets.Add(Before:=Worksheets(1))
ws.Name = "Combined"
Application.DisplayAlerts = False
Dim rng As Range, rng2 As Range
Set rng = Worksheets(2).Range("A1").CurrentRegion
rng.copy ws.Range("A1")
Dim i As Integer
Dim wsCopy As Worksheet, rngCopy As Range, rngDest As Range
For i = 3 To Worksheets.Count
Set wsCopy = Worksheets(i)
With wsCopy
Set rngCopy = .Range("A1").CurrentRegion
Set rngCopy = rngCopy.Offset(1, 0).Resize(rngCopy.Rows.Count - 1)
End With
With ws
Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
rngCopy.copy rngDest
Next i
' Delete original worksheets
For Each ws In Worksheets
If ws.Name <> "Combined" Then ws.Delete
Next ws
I have 3 worksheets. "Dept", "Saving", and "TimeDmnd" they all have the same header.
thank you.
microsoft-excel vba macros
microsoft-excel vba macros
asked Jan 30 at 6:20
geegee
344
asked Jan 30 at 6:20
geegee
344
asked Jan 30 at 6:20
geegee
344
asked Jan 30 at 6:20
geegee
344
asked Jan 30 at 6:20
geegee
344
344
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by addingrngCopy.Rows.Countto it) instead ofSet rngDeststatement.
– Akina
Jan 30 at 8:12
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59
add a comment |
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by addingrngCopy.Rows.Countto it) instead ofSet rngDeststatement.
– Akina
Jan 30 at 8:12
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by adding
rngCopy.Rows.Count to it) instead of Set rngDest statement.– Akina
Jan 30 at 8:12
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by adding
rngCopy.Rows.Count to it) instead of Set rngDest statement.– Akina
Jan 30 at 8:12
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1399954%2fmerge-multiple-worksheets-into-one-worksheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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.
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1399954%2fmerge-multiple-worksheets-into-one-worksheet%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
everytime I run this code it overwrites some of the data I cannot reproduce this. As a recommendation - store/recalculate current last row number (and build the destination address) on the destination worksheet (by adding
rngCopy.Rows.Countto it) instead ofSet rngDeststatement.– Akina
Jan 30 at 8:12
Is only one row being overwritten?
– Mark Fitzgerald
Jan 30 at 10:26
on Dept sheet I have 10 rows of data and on Saving sheet I have 25 rows and on the last sheet which is Timedmnd I have also 10 rows. And if I run the macro I only have 1 sheet that have only 25 rows Timedmnd overwrites 10 rows of Dept and Saving sheets data.. sorry I'am bad at english
– gee
Jan 31 at 0:59