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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,609
Messages
5,512,401
Members
408,889
Latest member
2vbr

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top