CODEWITHSUNDEEP
vbaexcel
Arlen Beiler
Arlen Beiler

Reputation: 15866

How to speed up copying cells in Excel vba

I have this Excel VB code, and everytime after it executes this line, it pauses for half a second:

Worksheets(ws.Name).Range("A" & i & ":G" & i).Copy _
Destination:=Worksheets("Sheet1").Range("A" & emptyCell)

Can someone tell me what it is doing, and how I can prevent it from taking so long?

I have Microsoft Excel 2007 on Windows XP Pro

Update: If I paste it by hand, it waits about the same amount of time.

Upvotes: 1

Views: 2768

Answers (2)

Fink
Fink

Reputation: 3436

It all depends on the specs of the hardware (hard drive, memory, network connection, ect). If its running on a network, that will be the biggest bottleneck. There is nothing inherently wrong with the code.

You can also try speeding up the routine by turning the workbooks calculation to manual, and turning off screen updating. If the calculation is automatic it will calculate on every copy call.

Upvotes: 2

dendarii
dendarii

Reputation: 3088

It's copying a range of cells from the worksheet assigned to the variable ws and pasting it to Sheet1.

The range it is copying is from A:G in the row number assigned to the variable i.

It is pasting the range into column A in Sheet1 in the row number assigned to the variable emptyCell.

You can prevent it by prefixing each line with an apostrophe.

However, you may first wish to explore WHY the code is doing that!

Upvotes: 0

Related Questions