speed up excel vba copying data from one sheet to another

Multi tool use
Multi tool use












1















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.










share|improve this question

























  • 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 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











  • @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
















1















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.










share|improve this question

























  • 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 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











  • @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














1












1








1








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.










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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











  • @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






  • 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











  • @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












1 Answer
1






active

oldest

votes


















0














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





share|improve this answer


























  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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





share|improve this answer


























  • 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
















0














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





share|improve this answer


























  • 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














0












0








0







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





share|improve this answer















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






share|improve this answer














share|improve this answer



share|improve this answer








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



















  • 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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
z1QvEIGZUh3m3BnoT

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas