Best way to copy value from one range to other

cwng3

New Member
Joined
Nov 7, 2005
Messages
31
Hi,

I have a spreadsheet to which has to
1/ copy some data from another spreadsheet
2/ copy some data from other cell with formula in the same spreadsheet

For 1/, the existing macro is to open the external spreadsheet and copy the value

For 2/, we are just using range.select, range.copy and then range copy and paste value function

As a result, it cause lots of screen refreshing and spend lot of time.

Is there any better way to do these ?

Thanks a lot.

Tom
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Tom

Can you post the current code?

You can turn off screen updating like this.
Code:
Application.ScreenUpdating = False
' your code here
Application.ScreenUpdating = True
 
Upvote 0
Hello,

try

Code:
Application.Screenupdating=false

at the beginning

Code:
Application.Screenupdating=true

at the end.

Does it make a significant difference?
 
Upvote 0
Thanks for the reply

Yes I know we can turn it off by set the screen update. But I want to see if any better way to do it.

Actually, the original coding is done by other who used Excel Macro Recorder to do it.

I think there should be other more effective way.
 
Upvote 0
Well post the code, at least some of it.

It's hard to recommend any changes without seeing what you currently have.:)
 
Upvote 0
Here is the macro coding (the real one is left in the office and I just use recorder to simulate again)

Thanks a lot.

----------------------------------------------------------------------------
Workbooks.Open Filename:="E:\temp\Excel\test\RESU05.xls", _
UpdateLinks:=0
Range("A3:A13").Select
Selection.Copy
Windows("Book2").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B6:B8").Select
Application.CutCopyMode = False
Selection.Copy
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Upvote 0
You don't need to select/activate ranges/workbooks to work with them.

Try this, untested, code.
Code:
Dim wb As Workbook

Set wb = Workbooks.Open(Filename:="E:\temp\Excel\test\RESU05.xls")

wb.ActiveSheet.Range("A3:A13").Copy
Workbooks("Book2.xls").ActiveSheet.Range("D3").PasteSpecial Paste:=xlPasteValues

Workbooks("Book2.xls").ActiveSheet.Range("B6:B8").Copy
Workbooks("Book2.xls").ActiveSheet.Range("H3").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
Thanks. So we have to open the spreadsheet and use copy and paste value function in order to achieve this ?

Actually, I am afraid of the locking problem if we need to open the excel file.

Thanks
 
Upvote 0
Sorry I don't understand what you mean, what do you actually want to know.

You can just close the workbook like this.
Code:
wb.Close
 
Upvote 0

Forum statistics

Threads
1,226,588
Messages
6,191,891
Members
453,685
Latest member
leoj9090

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top