What is paste specifically in Excel

Paste - command very slow!
authormessage
Shaunee
Excel VBA beginner


Posted:
11 March 2014, 12:31
First name:

       Version: Office 2010

Hello my dears,

unfortunately my code doesn't want to be exactly like me

I wrote the following macro to copy the cells once ... So the macro is really only there to be executed once - then I throw it away again

Code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
For i = 159 To 290
Cells (i, i + 2) .Select
Selection.Copy
Cells (i + 1, i + 3) .Select
ActiveSheet.Paste
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



Up to i = 150 everything worked out great ... Sure, the whole thing takes a while, but not that long ... And as I said, waiting a few seconds is not that bad for me.

From line 150 it started, however, that he really hung on a line for minutes. Both the editor and Excel then hung up in between.
I then went through the whole thing step by step with the debugger and found that it was attached to the following line:



Does anyone know why?
And why only from 150?
I've used the code a few times before and never had any problems ...

Have I flooded some storage or something?

As I said, I don't need any quick code, but one that works and won't crash my Excel

I hope you can help me!

Lg,
Shaunee
Isabelle :-)
Human


Posted:
March 11, 2014, 12:57 pm
First name:
Location: Western spiral arm of the galaxy

       Version: Office 2010

Hello,

so better?

Code:
Public Sub test ()
Dim i As Long
With application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Cells (159, 161). Copy

For i = 159 To 290
Call ActiveSheet.Paste (Destination: = Cells (i + 1, i + 3))
Next

With application
.CutCopyMode = False
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

_________________
LG Isi

Membership in the forum increases your chance of an answer from me by 99.999%
Shaunee
Excel VBA beginner


Posted:
March 11, 2014, 13:02
First name:

slowboarder
You can freely change the rank in the profile


Posted:
March 11, 2014, 1:10 pm
First name:

       Version: Office 2010

Hi
rewrite the code like this:

Code:
For i = 159 To 290
Cells (i, i + 2) .Copy
Cells (i + 1, i + 3) .PasteSpecial xlpasteall
Next


or even shorter:

Code:
For i = 159 To 290
Cells (i, i + 2). Copy Destination: = Cells (i + 1, i + 3)
Next


ActiveSheet.Paste is for pasting from the general clipboard and also inserts images or the like or data that has been copied from non-Excel files.
For pasting pure Excel content, it is better to use PasteSpecial ("especially for Excel") or Copy-Destination.

Greetings daniel
Shaunee
Excel VBA beginner


Posted:
March 11, 2014, 1:14 pm
First name:

       Version: Office 2010

Uuuand it cracks again

I have a very strong suspicion that this is no longer the code ...
As I said, everything is very slow. Even when scrolling, it takes a few seconds until the picture "follows" ...

What have i done x)

**** EDIT
Assumption confirmed!
I just tried to use .ClearContant instead of .Copy ... Actually led to a big crash ...
But it is only this table that is so slow.
All others still work.
I also have no Selection_Change or the like in any of the worksheets ...
guest



Posted:
March 11, 2014, 1:35 pm
First name:

       Version: Office 2010

Hello

Have you ever saved this folder as an Excel 97-2003 folder?
Even if you now have an .xlsm
made of it.
This can lead to problems with over 256 columns
create a new folder and copy manually
only the contents (not the whole sheets) in the new folder.
Shaunee
Excel VBA beginner


Posted:
March 11, 2014, 3:49 pm
First name:


       Version: Office 2010

Hey

Excel just solved my problem.
It crashed and then offered me the "repaired" file, which corresponded to my file without any conditional formatting.

Then I let the macro run - everything was fine.
Even after I reinstalled the conditional formatting, everything was completely fine.

If anyone has an explanation for this - I would be grateful! Otherwise the problem is solved.
    UpAll times are
GMT + 1 hour
You can Post in this forum.
You can Reply to posts in this forum.
You can your posts in this forum Not to edit.
You can your posts in this forum Not Clear.
You can to polls in this forum Not participate.
You can Files in this forum Not Post
You can Download files in this forum

Related topics
Forum / Topics reply author Calls last contribution
Excel formulas: If then link command with INDEX formula3KarstenL176Apr 10, 2013, 3:12 pm
hiob
Excel formulas: Large matrices with VLOOKUP -> File unusable slowly13chewing gum110712 Aug 2012, 3:26 pm
guest
Excel formulas: sum product 19 columns - slow11M_R_87283Apr 19, 2012, 8:10 am
M_R_87
Excel formulas: External references - Excel extremely slow2guest695Jul 19, 2011, 3:42 pm
kami2450
Excel Formulas: Reference Command in Excel Mobile8sgysbers690March 10, 2011, 10:37
sgysbers
Excel formulas: Excel problem with the command (sum = page! A8)2Braini120911 Jul 2010, 4:54 pm
Braini
Excel formulas: VBA command1frantthetank290March 14, 2010, 9:02 pm
shift-del
Excel formulas: validation rule is not used for copy and paste.3becher2480620 Nov 2009, 10:33
becher24
Excel formulas: Excel 2003 -> 2007 Insanely slow!6elsr221901 Sep 2009, 1:17 pm
< Peter >
Excel formulas: Command à là: MAX ((E5-E4) :( AS5-AS4))6MatthiasDS401Apr 22, 2009, 4:39 pm
MatthiasDS
Excel formulas: Sum if command ??7Mr.Toby 1989686March 13, 2009, 10:11
neopa
Excel formulas: Value Copy Paste via VBA2Tester11111903Jan 15, 2009, 3:14 pm
Mac4