Copy data very slow

[ManvinderKaur]

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

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

 

[Blade Hunter]

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:

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]

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]

Not easily, do a search on the board for "undo stack" it is not an easy process.

 

[VoG]

No, not easy at all. See http://spreadsheetpage.com/index.php/site/tip/undoing_a_vba_subroutine/

 

[Blade Hunter]

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]

a slight variation,

Private Sub cmdCopyMaterialType_Click()
  Worksheets("2.1 Production Efficiency").Range("D15:D200").Value = Worksheets("5.1 Production Efficiency").Range("D15:D200").Value
End Sub

 

[ManvinderKaur]

Thanks all I really appreciate your help...