Make Copy and Paste Run Faster with Assignments

[norts55]

I have a lot of different recorded macros that do a lot of copying and pasting. I never really cared about the fact that these macros are probably not the most efficiently written code. But I watched a video about making your code run 1000 times faster and it has me thinking that maybe I should try to write the code better than what just gets recorded during the record macro - because some of my macro are slow.
Below is a simple recorded code. The video talked about assignments but I've tried to modify this code to what the person in the video was doing with no luck.
Is there a way to do assignments with this code?

    Sheets("Bid Sheet").Select
    Range("K2").Select
    Selection.Copy
    Sheets("Bid Sheet-Org").Select
    Range("K2").Select
    ActiveSheet.Paste

 

[CephasOz]

You can do this in either of two ways, depending on whether you want to copy everything (the first one) or just the value (the second one):

    Sheets("Bid Sheet").Range("K2").Copy Destination:=Sheets("Bid Sheet-Org").Range("K2")

or

    Sheets("Bid Sheet-Org").Range("K2").Value = Sheets("Bid Sheet").Range("K2").Value

There is no need to select a sheet or a range.

 

[Alex Blakenburg]

I assume you are talking about Paul Kelly's video

CephasOz 2nd example is what Paul refers to as being assignment.
This method only works if you want to transfer the value of the cell(s) to another cell(s).
Your recorded macro is a full copy-paste which copies everything from the cell ie the content whether that be a value or formula and all the other characteristics such as Font, Fill Colour, Borders etc. An assignment really on works well with Values.

Converting from a recorded macro
1) You want to avoid Select & Activate - eg CephasOz's first example does this
2) Use assignment if you only want to copy across the values
3) Turn off ScreenUpdating and Calculations

 

[norts55]

Thank you for the responses. Yes, that is the video. It's very well done, just a little above my knowledge.

I used CephasOz's first example and it works great. However, I have now noticed some of the recorded macros are doing a paste special. Is there a way to make this more efficient? - I can't decipherer where the paste is happening. - It appears it just happens.

   Sheets("Bid Sheet").Select
    Range("X2").Select
    Selection.Copy
    Sheets("Bid Sheet-Org").Select
    Range("V1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Alex Blakenburg]

I am not sure what you mean by "can't decipherer where the paste is happening" but see if this helps

Sub CopyPasteSpecial()

    ' Replace this
   Sheets("Bid Sheet").Select
    Range("X2").Select
    Selection.Copy
    
    'With this (copy)
    Sheets("Bid Sheet").Range("X2").Copy
    
    ' Replace this
    Sheets("Bid Sheet-Org").Select
    Range("V1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    'With this (paste)
    Sheets("Bid Sheet-Org").Range("V1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

 

[norts55]

Thank you, that explains a lot.
One other thing I am finding. A lot of times, I am using select to select sheets then run another application. Is there a more efficient way to write this? I have tried to just drop the .select, but that gives me an error.

    Sheets("Bid Sheet").Select
    Application.Run "'" & ActiveWorkbook.Name & "'" & "!Fix_Font_Color"

 

[CephasOz]

If you are just doing copy/paste operations, there is no need to select or activate a particular worksheet. But - if you are doing something that relies on a particular worksheet being the ActiveSheet - then you will need to do that first. Where is Fix_Font_Color stored, and what does the code look like?

 

[norts55]

Ok, that is making sense.
Fix_Font_Color is actually a few macros I have stored in my workbook.

 

[CephasOz]

You can't make your code more efficient for your call to Fix_Font_Color. If it requires "Bid Sheet" to be the active worksheet, else the code gives an error, then you must Select it before you call Fix_Font_Color.

It's only where you are moving or interrogating data that you should avoid doing a Select for either worksheet or cell.

 

[norts55]

Thank you very much. This has been a huge help.