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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Not easily, do a search on the board for "undo stack" it is not an easy process.
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,751
Messages
5,833,487
Members
430,211
Latest member
Vandermeer

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
Top