Copying part of a worksheet in one workbook to another workbook

Hi John
You ask a very, very open question, and without more detail of what you are doing, sample data, or a file it is very difficult to answer with more than just a few basic points...

_1) You use AcriveSheet a lot, - maybe you have the code from a macro recording? You do not need to use it so much, and generally better practice not to reference things using Active things. So start off by dimensioning things and reference things more explicitly.

_2). If you want to avoid being asked by excel ( assuming you would answer with yes ) then you put a code pair around the bit ...

Please Login or Register  to view this content.

_3 ) It is very bad practice to use On Error Resume Next for large bits of code..you should only use, if at all, in small parts of code for very predictable errors. See here:
http://excelmatters.com/2015/03/17/on-error-wtf/
https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo

_ 4 ) The following code may not work, as i do not know exactly what you or doing or have any files, Data, or know what named ranges you are using, I do not understand the last bit of your code ( looks like you are calling some procedures) , etc. etc. , but it may help demo a few basic points.

Please Login or Register  to view this content.

Alan

Hi John,

Originally Posted by LoftySuth

....becoming more and more aware of the limitations of my amateur coding and of my need for advice like yours.....
Once again many thanks

You’re welcome. ( Actually i am only just really a beginner myself, or rather part time VBA Amateur)
Alan

BTW. then while I am here

Originally Posted by LoftySuth

......
The On Error Resume Next was there to cover the situation where one or some of the named ranges were empty. I thought I had to put the On Error Goto 0 at the end of the procedure....

The whole On Error Goto Stuff is a made IMO a bit unnecessary tricky to explain because it uses strange terms and weirdly worded Statements. The second link I gave on Errors is my own notes i wrote to try to get it clear in my thick head. ( The first link is better, written by a Pro )
The Error Statement On Error Resume Next ( which is also one of two VBA “Error Handlers” ) almost says what it does. In simple English it means VBA carries on at the line after the Error should have occurred. So it suppresses it. VBA goes happily on ignorant of what happened ( or didn’t ). That could be then a disaster. It is like first thing in the morning at work covering up your smoke detector in the canteen and pulling the plug on the live link to the Fire station for the whole Day just in case anyone ever burns some Chips.
On Error Goto 0 ( which is another one of four main Error Statements ) is a stupidly worded Statement IMO. It goes nowhere as far as anyone knows. It should be written Turn The Error Handler Off, which in simple English is what it does
So coming back to the Chips. If you do not mind burnt chips ( very unhealthy but i like the taste of burnt chips )

Please Login or Register  to view this content.

In 99% of cases you can find a better way than using an Error Handler for an expected error. There is almost always a way to check for the thing beforehand that might cause the error. –

Please Login or Register  to view this content.

The other Error Handler, On Error Goto Somewhere does usually go to somewhere ( you must specify somewhere ) and then as comparison is often done at the start of the day to take care of all unexpected errors. As a comparison it is as if you choose not to rely on the default Error handling ( which Tells you what / where caught fire and organising immediate building evacuation ), but instead for example you choose in the case of a fire to grab the cat and grab your back - up discs before you leave the building when ( or if at all ) at your own discretion**.

Please Login or Register  to view this content.

There are a few more subtle points then that ...( .....**like instead of leaving the building you can put the fire out and / with another IMO stupidly written Error statement On Error Goto -1 .......or possibly repair the damage ...)... but that is the basics

Hi John,
I had this reply for you some time ago, but could not get access to ExcelForum, like many... Hope I can get this in quick before the site is down again...


As i mentioned your original question was very open, and not knowing exactly what you are doing I just tried to give some quick general ideas, not a specific solution.

_1 ) But the typo with the Workbook / Worksheet was stupid of me, sorry about that. Here some variations of what it should have been...

Please Login or Register  to view this content.

_2 ) It was also a bit careless of me in quickly making a suggestion to do this as well

Please Login or Register  to view this content.

_ again in haste i was just suggesting not to rely on the implicit default of
Range
( which usually### refers, by default, to the Active sheet if the code is in a Normal Module or if the code is in a sheet module than refers, by default to that Sheet. ( ### Actualy it is a bit more complicated - In a normal module, an unqualified Range equates to Application.Range, not ActiveSheet.Range, which is usually the Active sheet
http://www.mrexcel.com/forum/excel-q...ml#post4038308
but that all starts getting involved ( and out of my depth !))

So anyway, I was just demonstrating the idea that it is better, generally, to always qualify the Range, and to do something like this instead:
Ws.Range

But here in the particular full code line, the same applies to Cells , so I should have done this

wsVIn.Range(wsVIn.Cells(2, 1), wsVIn.Cells(2 + x, 9)).Copy

or this

Please Login or Register  to view this content.

And you can “mix” the conventions

Please Login or Register  to view this content.

The problem is the area is so vast i could write a book, and even then not cover everything.
Here is a good Blog, and a couple of good Threads on Range referencing
http://excelmatters.com/referring-to-ranges-in-vba/
http://www.excelforum.com/excel-new-...to-arrays.html
http://www.excelforum.com/excel-prog...e-objects.html

Alan

BTW.. worth checking this ( your settings, or User Control Panel )
http://www.excelforum.com/usercp.php
to check replies, because of the forum software problems with, amongst other things E-mail Notifications of replies

I am not sure when I can reply further – The ExcelForum Software is freaking out with me again just now and i have virtually no access )

Hi John,
Assuming you are using the With VistaIn bit because you are referencing those Ranges you give in the Workbook VistaIn, then you need the extra . ( Dot ) bit. - That is basically what the With End With pair means : - everything within this With End With pair where you type .Something is seen by VBA as VistaIn.Something.
But you are allowed to reference other things within the With End With pair as well without using the . ( Dot ) before. Then they “go” where you specifically reference them. So in your code snippet you are referencing
Range
Without giving a specific Workbook, which will go to the default, as I explained in post #6.
So I am guessing that the code looks for those named ranges somewhere other than the workbook VistaIn, and errors as it does not find them there

So I guess you actually wanted to do this

Please Login or Register  to view this content.

Alan

P.s. Did you get Email Notification of this reply??

Hi John,

I did notice i may have got it a bit wrong!!! ( I think ). Again confusing workbooks and worksheets a bit !!

You cannot reference a Range usually with
Workbooks(" " ).Range !!!!

I forgot the in-between sheet reference!!!

Workbooks(" " ).Worksheets(“ “).Range

So I thought you may need in that With End With pair

.Worksheets(“ “).Range


But, as you said, there may be some subtitles with named ranges that i do not understand. I have never used named ranges.
I may be getting a bit of my depth here. Especially working “blind” at a distance.

Maybe i need to try to run your code with your Workbooks. I am not sure how practical that may be due to the Paths you are using etc.

I will try to look again later if i have time. But it think i may be losing this one a bit, Sorry.

I think it is the named Range stuff that is somehow putting a spanner in the works , and i lack experience there: How about rewriting your code not using Named ranges first, get that working, then go on to using named ranges?

Alan

Hi John,

One quick thing I noticed.

While experimenting for you I found that sometimes something of this form would not work

Range("ANamedRange").Clear

Strangely , in those cases, an alternative form of that code line did work

["ANamedRange"].Clear

I have not the slightest idea why this is the case – I only learnt about this alternative yesterday.

http://www.mrexcel.com/forum/excel-q...dangers-2.html

And in that Thread both these lines always worked. So I am very Puzzled!

Alan

Further, there could be some subtle problems related to where you have the Code, That is to say in a Sheet Module, or a Normal Module.
This is coming back to the Range referencing Theme
And that may be further complicated if you are trying to reference named Ranges in different Workbooks.
All very confusing!!

If you get me a couple of workbooks, give a full code, and explain again exactly what it is you are trying to do I may be able to take a better look at this issue.

Originally Posted by j_Southern

I have discovered that if, just before I run the code I bring Sheet("Orders_In") to the front, then it works. (With no stops before the named ranges)
In any other situation it does not work.
This is all true even if I replace
With ThisWorkbook with
With Workbooks("VistaGangV3.5.xlsm").

Surely I don't have to make Sheets("Orders_In") active before any code will work ?
.....

I have sometimes come across this sort of thing before. Often Activating a sheet and possibly selecting the first cell in a sheet sometimes overcame problems, but i never understood why !! Or there could be some connection to my point in Post #13 there!! Again, being very careful and Explicit in Range referencing seems to be very important. – by Activating a sheet (bringing to the front )you change where a
Range
Reference goes!!

_ ... just another note in passing.... there could be in addition a problem with "Scope" in your code, depending where your Code is........

http://www.mrexcel.com/forum/excel-q...ml#post4332606

Hi John,
So I think i have it ( and probably you too by now! )

So I made a new workbook and called it “VistaGangV3.5.xlsm”

Using the Names manager In Workbook “VistaGangV3.5.xlsm” I
_ 1 ) defined "Orders_In_Table" as ReferTo Sheet1!$A$1
_ 2 ) defined " Vista_In_Table" as ReferTo Sheet1!$A$2

I wrote this code, put it in a normal Module, and ran it.

Please Login or Register  to view this content.

As long as any sheet in workbook “VistaGangV3.5.xlsm” is selected, the code works , without error, and gives this in “Sheet1” of “VistaGangV3.5.xlsm”

Using Excel 2007

Row\Col	A
1	Somefink in Range("A1")
2	Somefink in Range("A2")

Sheet1

_ ..............

If I try to run the code with any other Workbook selected then I get the same error you did! That makes sense as VBA will look for those named ranges in the Active Workbook rather than Workbook “VistaGangV3.5.xlsm” ( and of course will not find them!! ) So I think that answers this Question

Originally Posted by j_Southern

I have discovered that if, just before I run the code I bring Sheet("Orders_In") to the front, then it works........Surely I don't have to make Sheets("Orders_In") active before any code will work ?
John

But further you do not have to make any particular sheet in a File active if you use the following Code version.
It will work regardless of what Workbook Sheet is Active ( Up Front ) as long as Workbook “VistaGangV3.5.xlsm” is open

Please Login or Register  to view this content.

_ ....

Let me know if you need help. I think i have learned a bit now about handling named ranges, etc
Alan