Copy data very slow

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
Hi
I am usiing following code to copy few hundred rows from one sheet to another sheet.
I want to undo that as well, so that overwritten values can be be retained with undo button ..
The following code is too slow
Code:
Private Sub cmdCopyMaterialType_Click()
Application.ScreenUpdating = False
Dim i As Integer
For i = 15 To 200
Worksheets("5.1 Production Efficiency").Range("D" & i).Value = Worksheets("2.1 Production Efficiency").Range("D" & i).Value
Next i
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Turn calculation off also with application.calculation = xlmanual then at the end back on with application.calculation = xlautomatic will speed up the way you are doing it but I question why you are looping the cells when you could just copy and paste as values in one go:

Code:
Private Sub cmdCopyMaterialType_Click()
Worksheets("2.1 Production Efficiency").Range("D15:D200").Copy
Worksheets("5.1 Production Efficiency").Range("D15:D200").PasteSpecial Paste:=xlPasteValues
End Sub
 
Upvote 0
Thanks for your help that is very helpfull ..I do not need to loop ..I am new to VBA so I thought that may be the only way to copy valuess...
Is there any way I can use Undo Button ?
Thanks
 
Upvote 0
OK, What you "could" do is every time you want an undo to be available, copy the page and hide the copy. Make the changes. If you need another undo available do the same, you could theoretically build up as many undo pages as you want.

If the user chooses to undo, simply delete the sheet and rename the backup tab you need restored.

Have a macro set up on close to delete all the backup tabs. Not sure how that would go with saving though, you would end up with a lot of redundant hidden tabs effectively bloating your sheet. You could intercept a save alerting the user that saving will remove all undo data then remove the sheets before a save.

Again, not an easy task but an option.
 
Upvote 0
a slight variation,
Code:
Private Sub cmdCopyMaterialType_Click()
  Worksheets("2.1 Production Efficiency").Range("D15:D200").Value = Worksheets("5.1 Production Efficiency").Range("D15:D200").Value
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,058
Messages
6,105,656
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top