[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Must be a quicker way

Hi,
I have a section of code that is within a larger vba procedure.
The procedure was fairly slow in executing so I commented out section by section until I found the part that was causing the slow down.
It's this;

    WKS2.Range("HonWkBase").ClearContents
    WKS2.Range("A:A").Value = WKS1.Range("J:J").Value
    WKS2.Range("B:B").Value = WKS1.Range("M:M").Value
    WKS2.Range("C:C").Value = WKS1.Range("O:O").Value

Is there a way of writing this so it executes quicker?

Thanks in advance
0
Stephen Byrom
Asked:
Stephen Byrom
  • 2
  • 2
1 Solution
 
Swapnil NirmalCommented:
Why dont you use copy paste? any reason?

WKS2.Range("HonWkBase").ClearContents

WKS1.Range("J:J").copy
WKS2.Range("A1').select
Activesheet.Paste

repeat above code for all 3 columns
0
 
Stephen ByromAuthor Commented:
Thanks for pointing me in the right direction of copy/paste.
I actually did this;

    WKS2.Range("HonWkBase").ClearContents
    WKS1.Range("J:J").Copy
    WKS2.Range("A1").PasteSpecial xlPasteValues
    WKS1.Range("M:M").Copy
    WKS2.Range("B1").PasteSpecial xlPasteValues
    WKS1.Range("O:O").Copy
    WKS2.Range("C1").PasteSpecial xlPasteValues

It worked better than the select method, but without your input I would have sat here another couple of hours.
Thanks again
0
 
SteveCommented:
OK, has been answered prety fast, but if copying using VBA:

WKS1.Columns("J").Copy WKS2.Columns("A")
WKS1.Columns("M").Copy WKS2.Columns("B")
WKS1.Columns("O").Copy WKS2.Columns("C")

Open in new window

0
 
Stephen ByromAuthor Commented:
Sweet!
Even quicker!
I'll ask something else soon and award you the points.
Thanks for taking the time
0
 
SteveCommented:
there is no need to "ask something else" (not quite how the site works).

I am satisfied with the sentiment and the thanks along with knowing you get the best solution.

:)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now