Necessary to open up one workbook in order to copy information to another workbook?

Hi all,

Just curious if it's at all necessary to physically open a spreadsheet in order to copy information to another workbook? Could one not possibly just list the range and file path you want copied?

Currently my code is having to open a file, select contents and then paste into another workbook on a specific tab and then close that file. I'd preferably not want the user to see the file physically opening in the toolbar and then closing if even just for the slightest fraction of a second.

Please Login or Register  to view this content.

No, it needs to be opened. Hide the action by switching off ScreenUpdating

Please Login or Register  to view this content.

You can use ADO

That's why I didn't suggest it at first

And that is why I come up with this one:

Please Login or Register  to view this content.

Oh wow i didn't think it was possible with so little code!

I've tried it out but got a debug error, should probably have put in the exact ranges i'm working with. I've most likely applied the incorrect ranges as i'm not 100% confident i understand what the code is trying to do, any help?

My actual code:

Please Login or Register  to view this content.

Applied snb code:

Please Login or Register  to view this content.

As Cutter already said: we try to accommodate your wishes as painstakingly as possible.
The reason you started this post is to prevent users seeing what is happening in the background. Then we should apply code that does exactly that sort of thing. If we want the code to be as fast as possible, we should preferably avoid the 'copy' method. Instead we write directly from one workbook to the other (avoiding the clipboard).
To do that we need 2 identically sized ranges. And also I do not believe that you need all rows from A9:X9 downwards to be copied.

Please Login or Register  to view this content.

to answer the other question, rightly said i don't need to copy all the way to last row 65536. I did this as the row length varies on a monthly basis but i'll look into a fix for that if i can crack this query.

to add:

I've tried only copying ranges A1:C7 and pasting into sheet 5 A1:C7 just as a test but once again no luck. I'll keep cracking away at it..

A variable approach:

Please Login or Register  to view this content.

NB. Do not use values, but Value

Values was my fault (in my posts). you were too obedient copying it. . I made a correction in the posts.