Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,441
- Office Version
- 365
- Platform
- Windows
Calling all VBA efficiency experts! Here is a multi-part question:
I have read that in speeding up Excel macros, avoid the use of “Copy” and “Paste” whenever possible.
For example, use:
Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value
Instead of:
Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)
My first question has two parts:
1. Can you do something similar when copying a whole sheet of unknown size to another sheet? If so, how?
2. Can you do something similar when just copying cell FORMATTING, not values? If so, how?
My second question is this:
I also read that putting “Application.EnableEvents=False” at the beginning of your code may speed it up. What does this do? What are “events”?
Thanks.
I have read that in speeding up Excel macros, avoid the use of “Copy” and “Paste” whenever possible.
For example, use:
Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value
Instead of:
Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)
My first question has two parts:
1. Can you do something similar when copying a whole sheet of unknown size to another sheet? If so, how?
2. Can you do something similar when just copying cell FORMATTING, not values? If so, how?
My second question is this:
I also read that putting “Application.EnableEvents=False” at the beginning of your code may speed it up. What does this do? What are “events”?
Thanks.