VBA copy and paste - the fast way

[malymato]

Hi there,
i know there are lot of threads about this. I need to copy only values. So we did something like this to avoid clipboard:
Worksheets("Sheet1").Range("C5:C31").Value = Worksheets("Sheet2").Range("A5:A31").Value
Worksheets("Sheet1").Range("E5:E31").Value = Worksheets("Sheet2").Range("B5:B31").Value
Worksheets("Sheet1").Range("G5:H25").Value = Worksheets("Sheet2").Range("R4:S24").Value
Worksheets("Sheet1").Range("F12:F25").Value = Worksheets("Sheet2").Range("Q11:Q24").Value
Worksheets("Sheet1").Range("I12:I20").Value = Worksheets("Sheet2").Range("K11:K19").Value
Worksheets("Sheet1").Range("J12:J20").Value = Worksheets("Sheet2").Range("N11:N19").Value
Worksheets("Sheet1").Range("I26:I27").Value = Worksheets("Sheet2").Range("K20:K21").Value
Worksheets("Sheet1").Range("J26:J27").Value = Worksheets("Sheet2").Range("N20:N21").Value

These are just a sample of rows. We have much more to copy. Can i write it more efficient. The range never changes it is still the same.

Thanks for any help.

 

[Joe4]

Welcome to the Board!

If there was some sort of predictable or repeatable pattern to the ranges you are referencing, then we could probably write some loops to shorten the code.
But I do not see that at all in your example. It looks like the ranges jump around everywhere, with no rhyme or reason.

If that is the case, I do not think you can get around having to list each one out separately.
The good news is, you will only need to go through that exercise once, as then you will have your code written and can re-use it over and over.

The only other thing I can think of is if you have some list of all the ranges to copy from/to, like in a spreadsheet somewhere, and you could loop through those.
The only advantage to doing it that way is you can make the process more dynamic, i.e. if amything changes, you only need to changes that list and not the VBA code.

 

[Alex Blakenburg]

I couldn't see any pattern to work with either the only thing that I might change is as per the below, which should make it a little faster.
(and probably use variables for the worksheets but that probably wouldn't impact the speed)

With Worksheets("Sheet1")
    .Range("C5:C31").Value = Worksheets("Sheet2").Range("A5:A31").Value
    .Range("E5:E31").Value = Worksheets("Sheet2").Range("B5:B31").Value
    .Range("G5:H25").Value = Worksheets("Sheet2").Range("R4:S24").Value
    .Range("F12:F25").Value = Worksheets("Sheet2").Range("Q11:Q24").Value
    .Range("I12:I20").Value = Worksheets("Sheet2").Range("K11:K19").Value
    .Range("J12:J20").Value = Worksheets("Sheet2").Range("N11:N19").Value
    .Range("I26:I27").Value = Worksheets("Sheet2").Range("K20:K21").Value
    .Range("J26:J27").Value = Worksheets("Sheet2").Range("N20:N21").Value
End With

 

[malymato]

Yeah there is no pattern. Thank you guys for your suggestions.

 

[dmt32]

Yeah there is no pattern.

As there is no pattern a belated suggestion you may want to consider that reduces the need for all the hard coding is to create a simple table & read this in to a 2D variant array.

Copy following table to a worksheet in your workbook & name it "Copy Ranges"

06-10-2022.xls
	A	B
1	To	From
2	Sheet1	Sheet2
3	C5:C31	A5:A31
4	E5:E31	B5:B31
5	G5:H25	R4:S24
6	F12:F25	Q11:Q24
7	I12:I20	K11:K19
8	J12:J20	N11:N19
9	I26:I27	K20:K21
10	J26:J27	N20:N21
Copy Ranges

You will need to check I have the ranges correct in the table


Your code would then look like this

Sub malymato()
    Dim arr         As Variant
    Dim r           As Long
    With ThisWorkbook
        arr = .Worksheets("Copy Ranges").Range("A1").CurrentRegion.Value
        For r = 3 To UBound(arr, 1)
            .Worksheets(arr(2, 1)).Range(arr(r, 1)).Value = _
            .Worksheets(arr(2, 2)).Range(arr(r, 2)).Value
        Next r
    End With
End Sub

Solution is dynamic and should allow to add to the table as needed without any further changes to code.

Hope Helpful

Dave

 

[Joe4]

As there is no pattern a belated suggestion you may want to consider that reduces the need for all the hard coding is to create a simple table & read this in to a 2D variant array.

Copy following table to a worksheet in your workbook & name it "Copy Ranges"

06-10-2022.xls
	A	B
1	To	From
2	Sheet1	Sheet2
3	C5:C31	A5:A31
4	E5:E31	B5:B31
5	G5:H25	R4:S24
6	F12:F25	Q11:Q24
7	I12:I20	K11:K19
8	J12:J20	N11:N19
9	I26:I27	K20:K21
10	J26:J27	N20:N21
Copy Ranges

You will need to check I have the ranges correct in the table


Your code would then look like this

Sub malymato()
    Dim arr         As Variant
    Dim r           As Long
    With ThisWorkbook
        arr = .Worksheets("Copy Ranges").Range("A1").CurrentRegion.Value
        For r = 3 To UBound(arr, 1)
            .Worksheets(arr(2, 1)).Range(arr(r, 1)).Value = _
            .Worksheets(arr(2, 2)).Range(arr(r, 2)).Value
        Next r
    End With
End Sub

Solution is dynamic and should allow to add to the table as needed without any further changes to code.

Hope Helpful

Dave

Thanks Dave.

That is precisely what I was referring to when I said:

The only other thing I can think of is if you have some list of all the ranges to copy from/to, like in a spreadsheet somewhere, and you could loop through those.
The only advantage to doing it that way is you can make the process more dynamic, i.e. if amything changes, you only need to changes that list and not the VBA code.

 

[dmt32]

@Joe4
I must confess to being a little puzzled with your post – are you implying that I have taken your idea? Hope that’s not the case - we are all only here as a team to assist.

I belatedly only read OPs post & noted the comment “there is no pattern” so created the array solution I posted – If this is similar to what you were thinking then perhaps just a case of great minds think alike but still leaves me puzzled why you felt need to make the point to me.

All best

Dave

 

[Joe4]

@Joe4
I must confess to being a little puzzled with your post – are you implying that I have taken your idea? Hope that’s not the case - we are all only here as a team to assist.

I belatedly only read OPs post & noted the comment “there is no pattern” so created the array solution I posted – If this is similar to what you were thinking then perhaps just a case of great minds think alike but still leaves me puzzled why you felt need to make the point to me.

All best

Dave

No, not at all! I agree we are on the same team here. More just pointing out to the OP that is what that would look like (that we are talking about one-and-the-same thing).
Sorry if it appears that I was conveying a different message!

I wasn't going to worry about coming up with the code unless the OP expressed an interest in pursuing that, but I don't mind at all that you posted it.
It might actually be good for the OP to see what that code looks like (and I confess, the code that you came up with is most likely shorter than the code I would have come up with!).