speed up excel vba copying data from one sheet to another

Multi tool use
on one sheet I have rows of data from column A to column L.
I have a macro that, given user input, searches the rows, and then copy and pastes that row into a different (initally blank) sheet. The search will then continue, each time copying and pasting. Unfortunately, sometimes this involves copying & pasting 500 rows. Excel starts struggling at around 400 rows, is very slow and often just crashes.
I have read Slow VBA macro writing in cells but I am not sure if it applies.
Would creating a collection of the row numbers resulting from my search and then looping through and copying & pasting the corresponding row be any quicker than copying and pasting the row as soon as it has been 'found' (this is how it currently works)?
I wouldn't have thought so. So my question is, can I speed up this vba process of copying & pasting a large amount of rows?
edit:
nextblankrow=worksheets("findings").Range("A"&rows.count).End(xlup).row+1
Sheets("data").cells(J,1).EntireRow.copy sheets("findings").cells(nextblankrow,1)
so in the above code, the first line finds the next empty row in the "findings" sheet.
Then the second line copies the row in the "data" sheet which has been found to match the user input into the "findings" sheet.
After this, it goes back to the search until it has got to the end of data in the "data" sheet. But I have determied that it is the copying that is causing slowness and crashing.
Thank you very much.
excel vba excel-vba
|
show 4 more comments
on one sheet I have rows of data from column A to column L.
I have a macro that, given user input, searches the rows, and then copy and pastes that row into a different (initally blank) sheet. The search will then continue, each time copying and pasting. Unfortunately, sometimes this involves copying & pasting 500 rows. Excel starts struggling at around 400 rows, is very slow and often just crashes.
I have read Slow VBA macro writing in cells but I am not sure if it applies.
Would creating a collection of the row numbers resulting from my search and then looping through and copying & pasting the corresponding row be any quicker than copying and pasting the row as soon as it has been 'found' (this is how it currently works)?
I wouldn't have thought so. So my question is, can I speed up this vba process of copying & pasting a large amount of rows?
edit:
nextblankrow=worksheets("findings").Range("A"&rows.count).End(xlup).row+1
Sheets("data").cells(J,1).EntireRow.copy sheets("findings").cells(nextblankrow,1)
so in the above code, the first line finds the next empty row in the "findings" sheet.
Then the second line copies the row in the "data" sheet which has been found to match the user input into the "findings" sheet.
After this, it goes back to the search until it has got to the end of data in the "data" sheet. But I have determied that it is the copying that is causing slowness and crashing.
Thank you very much.
excel vba excel-vba
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
1
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building aunion
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.
– chillin
Dec 31 '18 at 12:20
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36
|
show 4 more comments
on one sheet I have rows of data from column A to column L.
I have a macro that, given user input, searches the rows, and then copy and pastes that row into a different (initally blank) sheet. The search will then continue, each time copying and pasting. Unfortunately, sometimes this involves copying & pasting 500 rows. Excel starts struggling at around 400 rows, is very slow and often just crashes.
I have read Slow VBA macro writing in cells but I am not sure if it applies.
Would creating a collection of the row numbers resulting from my search and then looping through and copying & pasting the corresponding row be any quicker than copying and pasting the row as soon as it has been 'found' (this is how it currently works)?
I wouldn't have thought so. So my question is, can I speed up this vba process of copying & pasting a large amount of rows?
edit:
nextblankrow=worksheets("findings").Range("A"&rows.count).End(xlup).row+1
Sheets("data").cells(J,1).EntireRow.copy sheets("findings").cells(nextblankrow,1)
so in the above code, the first line finds the next empty row in the "findings" sheet.
Then the second line copies the row in the "data" sheet which has been found to match the user input into the "findings" sheet.
After this, it goes back to the search until it has got to the end of data in the "data" sheet. But I have determied that it is the copying that is causing slowness and crashing.
Thank you very much.
excel vba excel-vba
on one sheet I have rows of data from column A to column L.
I have a macro that, given user input, searches the rows, and then copy and pastes that row into a different (initally blank) sheet. The search will then continue, each time copying and pasting. Unfortunately, sometimes this involves copying & pasting 500 rows. Excel starts struggling at around 400 rows, is very slow and often just crashes.
I have read Slow VBA macro writing in cells but I am not sure if it applies.
Would creating a collection of the row numbers resulting from my search and then looping through and copying & pasting the corresponding row be any quicker than copying and pasting the row as soon as it has been 'found' (this is how it currently works)?
I wouldn't have thought so. So my question is, can I speed up this vba process of copying & pasting a large amount of rows?
edit:
nextblankrow=worksheets("findings").Range("A"&rows.count).End(xlup).row+1
Sheets("data").cells(J,1).EntireRow.copy sheets("findings").cells(nextblankrow,1)
so in the above code, the first line finds the next empty row in the "findings" sheet.
Then the second line copies the row in the "data" sheet which has been found to match the user input into the "findings" sheet.
After this, it goes back to the search until it has got to the end of data in the "data" sheet. But I have determied that it is the copying that is causing slowness and crashing.
Thank you very much.
excel vba excel-vba
excel vba excel-vba
edited Dec 31 '18 at 12:34
newbie23
asked Dec 31 '18 at 12:09


newbie23newbie23
195
195
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
1
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building aunion
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.
– chillin
Dec 31 '18 at 12:20
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36
|
show 4 more comments
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
1
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building aunion
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.
– chillin
Dec 31 '18 at 12:20
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
1
1
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (
AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building a union
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.– chillin
Dec 31 '18 at 12:20
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (
AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building a union
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.– chillin
Dec 31 '18 at 12:20
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36
|
show 4 more comments
1 Answer
1
active
oldest
votes
Speed Up Copy/Paste Range
In case you didn't know, turning off (False) of Application.ScreenUpdating
and especially Application.Calculation
will increase the execution speed of your code, too.
Union Range Version
Sub CopyRangeToSheetUnion()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim rngU As Range ' Union Range
Dim i As Long ' Source Range Row Counter
With Worksheets(cVntSrc).Range(cStrSrcRange)
' Loop through each cell in column cIntColumn of Source Range and copy
' to Union Range if condition is met.
For i = 1 To .Rows.Count
If .Cells.Cells(i, cIntColumn).Value <> "" Then
If Not rngU Is Nothing Then
Set rngU = Union(rngU, .Cells(i, cIntColumn))
Else
Set rngU = .Cells(i, cIntColumn)
End If
End If
Next
End With
' Copy entire rows from Union range to Target Range.
If Not rngU Is Nothing Then
rngU.EntireRow.Copy Worksheets(cVntTgt).Range(cStrTgtCell)
Set rngU = Nothing
End If
End Sub
Array Version
Here is a sample with a condition which copies every row that doesn't have an empty cell in column "A" (I'll be posting a sample with a condition using the Union method shortly).
Sub CopyRangeToSheetArray()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
Dim vntTgt As Variant ' Target Array
Dim i As Long ' Source Array Row Counter
Dim j As Integer ' Source/Target Array Column Counter
Dim k As Long ' Target Array Column Count/Counter
' Paste the Source Range into Source Array.
vntSrc = Worksheets(cVntSrc).Range(cStrSrcRange)
' Count the number of rows that meet the condition.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
End If
Next
' Resize Target Array.
ReDim vntTgt(1 To k, 1 To UBound(vntSrc, 2))
' Reset Target Array Column Counter
k = 0
' Write from Source to Target Array.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
For j = 1 To UBound(vntSrc, 2)
vntTgt(k, j) = vntSrc(i, j)
Next
End If
Next
' Paste Target Array into Target Worksheet
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
End Sub
Appetizer
Here is a sample for copying a specific range without any condition. You can change (increase) the values in the constants section, play with it to see how fast it is and to better understand the concept. I'll be posting a sample with a condition shortly.
Sub CopyRangeToSheet()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
With Worksheets(cVntSrc)
vntSrc = .Range(cStrSrcRange)
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntSrc), UBound(vntSrc, 2)) = vntSrc
End With
End Sub
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
|
show 2 more comments
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%2f53987345%2fspeed-up-excel-vba-copying-data-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
Speed Up Copy/Paste Range
In case you didn't know, turning off (False) of Application.ScreenUpdating
and especially Application.Calculation
will increase the execution speed of your code, too.
Union Range Version
Sub CopyRangeToSheetUnion()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim rngU As Range ' Union Range
Dim i As Long ' Source Range Row Counter
With Worksheets(cVntSrc).Range(cStrSrcRange)
' Loop through each cell in column cIntColumn of Source Range and copy
' to Union Range if condition is met.
For i = 1 To .Rows.Count
If .Cells.Cells(i, cIntColumn).Value <> "" Then
If Not rngU Is Nothing Then
Set rngU = Union(rngU, .Cells(i, cIntColumn))
Else
Set rngU = .Cells(i, cIntColumn)
End If
End If
Next
End With
' Copy entire rows from Union range to Target Range.
If Not rngU Is Nothing Then
rngU.EntireRow.Copy Worksheets(cVntTgt).Range(cStrTgtCell)
Set rngU = Nothing
End If
End Sub
Array Version
Here is a sample with a condition which copies every row that doesn't have an empty cell in column "A" (I'll be posting a sample with a condition using the Union method shortly).
Sub CopyRangeToSheetArray()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
Dim vntTgt As Variant ' Target Array
Dim i As Long ' Source Array Row Counter
Dim j As Integer ' Source/Target Array Column Counter
Dim k As Long ' Target Array Column Count/Counter
' Paste the Source Range into Source Array.
vntSrc = Worksheets(cVntSrc).Range(cStrSrcRange)
' Count the number of rows that meet the condition.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
End If
Next
' Resize Target Array.
ReDim vntTgt(1 To k, 1 To UBound(vntSrc, 2))
' Reset Target Array Column Counter
k = 0
' Write from Source to Target Array.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
For j = 1 To UBound(vntSrc, 2)
vntTgt(k, j) = vntSrc(i, j)
Next
End If
Next
' Paste Target Array into Target Worksheet
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
End Sub
Appetizer
Here is a sample for copying a specific range without any condition. You can change (increase) the values in the constants section, play with it to see how fast it is and to better understand the concept. I'll be posting a sample with a condition shortly.
Sub CopyRangeToSheet()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
With Worksheets(cVntSrc)
vntSrc = .Range(cStrSrcRange)
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntSrc), UBound(vntSrc, 2)) = vntSrc
End With
End Sub
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
|
show 2 more comments
Speed Up Copy/Paste Range
In case you didn't know, turning off (False) of Application.ScreenUpdating
and especially Application.Calculation
will increase the execution speed of your code, too.
Union Range Version
Sub CopyRangeToSheetUnion()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim rngU As Range ' Union Range
Dim i As Long ' Source Range Row Counter
With Worksheets(cVntSrc).Range(cStrSrcRange)
' Loop through each cell in column cIntColumn of Source Range and copy
' to Union Range if condition is met.
For i = 1 To .Rows.Count
If .Cells.Cells(i, cIntColumn).Value <> "" Then
If Not rngU Is Nothing Then
Set rngU = Union(rngU, .Cells(i, cIntColumn))
Else
Set rngU = .Cells(i, cIntColumn)
End If
End If
Next
End With
' Copy entire rows from Union range to Target Range.
If Not rngU Is Nothing Then
rngU.EntireRow.Copy Worksheets(cVntTgt).Range(cStrTgtCell)
Set rngU = Nothing
End If
End Sub
Array Version
Here is a sample with a condition which copies every row that doesn't have an empty cell in column "A" (I'll be posting a sample with a condition using the Union method shortly).
Sub CopyRangeToSheetArray()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
Dim vntTgt As Variant ' Target Array
Dim i As Long ' Source Array Row Counter
Dim j As Integer ' Source/Target Array Column Counter
Dim k As Long ' Target Array Column Count/Counter
' Paste the Source Range into Source Array.
vntSrc = Worksheets(cVntSrc).Range(cStrSrcRange)
' Count the number of rows that meet the condition.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
End If
Next
' Resize Target Array.
ReDim vntTgt(1 To k, 1 To UBound(vntSrc, 2))
' Reset Target Array Column Counter
k = 0
' Write from Source to Target Array.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
For j = 1 To UBound(vntSrc, 2)
vntTgt(k, j) = vntSrc(i, j)
Next
End If
Next
' Paste Target Array into Target Worksheet
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
End Sub
Appetizer
Here is a sample for copying a specific range without any condition. You can change (increase) the values in the constants section, play with it to see how fast it is and to better understand the concept. I'll be posting a sample with a condition shortly.
Sub CopyRangeToSheet()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
With Worksheets(cVntSrc)
vntSrc = .Range(cStrSrcRange)
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntSrc), UBound(vntSrc, 2)) = vntSrc
End With
End Sub
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
|
show 2 more comments
Speed Up Copy/Paste Range
In case you didn't know, turning off (False) of Application.ScreenUpdating
and especially Application.Calculation
will increase the execution speed of your code, too.
Union Range Version
Sub CopyRangeToSheetUnion()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim rngU As Range ' Union Range
Dim i As Long ' Source Range Row Counter
With Worksheets(cVntSrc).Range(cStrSrcRange)
' Loop through each cell in column cIntColumn of Source Range and copy
' to Union Range if condition is met.
For i = 1 To .Rows.Count
If .Cells.Cells(i, cIntColumn).Value <> "" Then
If Not rngU Is Nothing Then
Set rngU = Union(rngU, .Cells(i, cIntColumn))
Else
Set rngU = .Cells(i, cIntColumn)
End If
End If
Next
End With
' Copy entire rows from Union range to Target Range.
If Not rngU Is Nothing Then
rngU.EntireRow.Copy Worksheets(cVntTgt).Range(cStrTgtCell)
Set rngU = Nothing
End If
End Sub
Array Version
Here is a sample with a condition which copies every row that doesn't have an empty cell in column "A" (I'll be posting a sample with a condition using the Union method shortly).
Sub CopyRangeToSheetArray()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
Dim vntTgt As Variant ' Target Array
Dim i As Long ' Source Array Row Counter
Dim j As Integer ' Source/Target Array Column Counter
Dim k As Long ' Target Array Column Count/Counter
' Paste the Source Range into Source Array.
vntSrc = Worksheets(cVntSrc).Range(cStrSrcRange)
' Count the number of rows that meet the condition.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
End If
Next
' Resize Target Array.
ReDim vntTgt(1 To k, 1 To UBound(vntSrc, 2))
' Reset Target Array Column Counter
k = 0
' Write from Source to Target Array.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
For j = 1 To UBound(vntSrc, 2)
vntTgt(k, j) = vntSrc(i, j)
Next
End If
Next
' Paste Target Array into Target Worksheet
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
End Sub
Appetizer
Here is a sample for copying a specific range without any condition. You can change (increase) the values in the constants section, play with it to see how fast it is and to better understand the concept. I'll be posting a sample with a condition shortly.
Sub CopyRangeToSheet()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
With Worksheets(cVntSrc)
vntSrc = .Range(cStrSrcRange)
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntSrc), UBound(vntSrc, 2)) = vntSrc
End With
End Sub
Speed Up Copy/Paste Range
In case you didn't know, turning off (False) of Application.ScreenUpdating
and especially Application.Calculation
will increase the execution speed of your code, too.
Union Range Version
Sub CopyRangeToSheetUnion()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim rngU As Range ' Union Range
Dim i As Long ' Source Range Row Counter
With Worksheets(cVntSrc).Range(cStrSrcRange)
' Loop through each cell in column cIntColumn of Source Range and copy
' to Union Range if condition is met.
For i = 1 To .Rows.Count
If .Cells.Cells(i, cIntColumn).Value <> "" Then
If Not rngU Is Nothing Then
Set rngU = Union(rngU, .Cells(i, cIntColumn))
Else
Set rngU = .Cells(i, cIntColumn)
End If
End If
Next
End With
' Copy entire rows from Union range to Target Range.
If Not rngU Is Nothing Then
rngU.EntireRow.Copy Worksheets(cVntTgt).Range(cStrTgtCell)
Set rngU = Nothing
End If
End Sub
Array Version
Here is a sample with a condition which copies every row that doesn't have an empty cell in column "A" (I'll be posting a sample with a condition using the Union method shortly).
Sub CopyRangeToSheetArray()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
Const cIntColumn As Integer = 1 ' Source Search Column
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
Dim vntTgt As Variant ' Target Array
Dim i As Long ' Source Array Row Counter
Dim j As Integer ' Source/Target Array Column Counter
Dim k As Long ' Target Array Column Count/Counter
' Paste the Source Range into Source Array.
vntSrc = Worksheets(cVntSrc).Range(cStrSrcRange)
' Count the number of rows that meet the condition.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
End If
Next
' Resize Target Array.
ReDim vntTgt(1 To k, 1 To UBound(vntSrc, 2))
' Reset Target Array Column Counter
k = 0
' Write from Source to Target Array.
For i = 1 To UBound(vntSrc)
If vntSrc(i, cIntColumn) <> "" Then
k = k + 1
For j = 1 To UBound(vntSrc, 2)
vntTgt(k, j) = vntSrc(i, j)
Next
End If
Next
' Paste Target Array into Target Worksheet
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntTgt), UBound(vntTgt, 2)) = vntTgt
End Sub
Appetizer
Here is a sample for copying a specific range without any condition. You can change (increase) the values in the constants section, play with it to see how fast it is and to better understand the concept. I'll be posting a sample with a condition shortly.
Sub CopyRangeToSheet()
' Source
Const cVntSrc As Variant = "Sheet1" ' Source Worksheet Name/Index
Const cStrSrcRange As String = "A1:J10" ' Source Range
' Target
Const cStrTgtCell As String = "A1" ' Target First Cell Range
Const cVntTgt As Variant = "Sheet2" ' Target Worksheet Name/Index
Dim vntSrc As Variant ' Source Array
With Worksheets(cVntSrc)
vntSrc = .Range(cStrSrcRange)
Worksheets(cVntTgt).Range(cStrTgtCell) _
.Resize(UBound(vntSrc), UBound(vntSrc, 2)) = vntSrc
End With
End Sub
edited Dec 31 '18 at 14:27
answered Dec 31 '18 at 12:57


VBasic2008VBasic2008
2,7352415
2,7352415
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
|
show 2 more comments
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
Thanks, but isn't the problem I have that my range isn't a countinuous block like "A1:J10"?
– newbie23
Dec 31 '18 at 13:05
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: I've edited my answer.
– VBasic2008
Dec 31 '18 at 13:28
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
@newbie23: The was an error in the array version: Const cIntColumn As Integer = 1. It was previously set to string. Sorry. I've added the Union Range Version.
– VBasic2008
Dec 31 '18 at 14:02
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
Thank you, I've done a union, & it's much much faster! Wish I could upvote & accept. Thanks again.
– newbie23
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
newbie23: You can accept.
– VBasic2008
Dec 31 '18 at 15:36
|
show 2 more comments
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%2f53987345%2fspeed-up-excel-vba-copying-data-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
dIQxmKLchTqvg lQ,7lxVzI3nURrzF,oz2fPIWMP3evLZYHbVeGmBV,xBLKs,ig5r9
Are you copying contiguous rows i.e. every row or are you checking for a condition and if it is met then you copy the row?
– VBasic2008
Dec 31 '18 at 12:19
1
Hard to say without looking at your code. Generally, you want to do all read/write operations involving the worksheet in one go (or at least in as few batches as possible). You might find filtering (
AutoFilter
) faster than looping through individual rows. Also, something else that might be useful is building aunion
of cells/ranges, which you might be able to copy-paste in one go. Or even working with arrays might be a feasible solution. But yeah, hard to be specific without looking at the code.– chillin
Dec 31 '18 at 12:20
@VBasic2008 it is the latter, so yes unfortunately the rows being copied are not in a continuous block.
– newbie23
Dec 31 '18 at 12:25
@chillin Please see my edit.
– newbie23
Dec 31 '18 at 12:35
You want to restrict the copying to the used range i.e. not copy after the last used column and not copy after the last used row. When you have determined the range, in a loop you check for the condition and write each row that meets the condition to an array and when done you paste the array into a calculated (from array size) resulting range.
– VBasic2008
Dec 31 '18 at 12:36