VBA to set cell value the same as another workbook (not copy & paste)

Hello,

I wondered if anyone could help with the following:

I use the following code to set the contents specific cells on a worksheet equal to cells on another worksheet within the same workbook.

Please Login or Register  to view this content.

This works fine without needing to copy and paste cells values (done to speed up as the number of rows can be quite large).
However, it does not seem to work when copying from another Workbook (unless I have a typo I haven't spotted).

e.g. the following results in "Run-time error '9': Subscript out of range

Please Login or Register  to view this content.

Can someone point out my error please?

Edit: Workbook2.xlsm is open at this point in the code
Edit2: Apologies - first time posting code - wasn't aware of Rule 3

Thanks.

Hi wahbob

You have to be very careful when you use that code form of ( pseudo code )
Range( cell1 , cell2 )

You have to be very careful that those cells being referenced in the bracket ( and also the Range - but you got that explicitly correct) are the ones that you think they are.

Depending on exactly where you have the code, and exactly what you are doing, the code may or may not work depending on where those cell references may ( try to ) go if you miss something out, and then you rely on the Implicitly implied thingy bob jobs

This mod may work for you. Here the Workbook reference is included just to be sure all references are given completely

Please Login or Register  to view this content.

Dave ( Alan ) Behold, I am the reincarnation of that famous Irish Comic :-)

ref
http://excelmatters.com/referring-to-ranges-in-vba/ - a few errors but short and easy to read
http://www.excelfox.com/forum/showth...eadsheet-cells - This and the next are close to perfect but you need a day to read through them
https://www.excelforum.com/tips-and-...eet-cells.html

Hello wahbob,
Thanks for the Rep Message feedback

By the way………
I am refreshing my VBA stuff after a long break just now. I practiced on your issue. Some of what I did might be of passing interest to you or anyone catching this Thread.
It concerns the different ways of doing the _ “Not Copy and Paste _ .Value” _ way , which is the way I almost always do these sort of things… or more specifically the ways of referring to cells_..

So you are using the “.. two argument “Range ( cell1 , cell2 )” way..”
_..The two argument “Range ( cell1 , cell2 )” way is a valid way, and often used. One reason is that you are making use in the brackets ( ) of a two ( digit ) arguments possibility to get at a cell.
This “Range ( cell( r0 , c0 ) , cell( r , c )” way will often be used if you have those cells co ordinates as numbers , - the r0 , c0 , r , c

I personally avoid that.
_ One reason is the problem like what you had. It seems to catch out many people even many Experts.

I think to some extent that lies in a misunderstanding of what is going on in that cell( _ , _ ) .
What you are doing , briefly is Specifying a Cell with the Worksheet.Cells and Range.Item Properties:
“……use the Worksheet.Cells property to return a Range object representing all the cells within a worksheet.…………., then use the Range.Item property to return a Range object representing one of those cells. “ https://powerspreadsheets.com/excel-...tem-Properties . People tend to get in the bad habit of forgetting that and / or missing bits out , relying on default things, and then coming up with incorrect concepts like a Cells( r , c ) type property…. Which you see written all over the place in books , Blog sites and the such , but which IMO only exists in the imagination of the people who wrote about it..
_ So the second reason I try not to use that two argument way is that I get fed up of trying to explain all that. ( Especially as the Experts get really pissed off when I tell them of their mistakes… )

As prerequisite to avoiding the “Range ( cell1 , cell2 )” way, I get in the habit of putting a simple Pubic Function code in a normal code module which converts a Column Number into a Column letter. ( Once you have done that you can even use it in a worksheet , which is a useful Function to have anyway. It surprises me Microsoft never gave one as standard )

So you initially need to drop this simple code in to a normal code module in your workbook, ( in your case the workbook where your other code is / codes are )

Please Login or Register  to view this content.

Having done that you can use various forms of the Range( single argument ) way

In the attached files , I chose and made up an arbitrary range in the Workbook2. Then I have 3 main codes, ( in the Workbook1 ). The second two code examples below use the Range( single argument ) way

Code 1. This is just a version of your code. (still using the two argument “Range ( cell1 , cell2 )” way)

Code 2. Is your code in a version of the Range( “single argument” ) way. (I have done 4 different code line variations of the equivalent of your main code line)

Code 3. Just out of interest, a variation allowing you to have Workbook2 closed or open. This is possible for a simple “Value, Not Copy paste thingy”, like wot we are talking about in this Thread
The syntax is similar to that used in my version of the Range( single argument ) way. This is another reason why I like this Range( single argument ) way, in particular the Application.Range variation of it which I am using. This variation is particularly robust and you can put the code in most places and it will usually work as you are being very explicit in the range referencing.
In addition, in a typical situation as yours, a variable, in your case, Row100A, can be omitted by virtue of the single cell ( initial Top Left ) reference utilising fixed vector relative referencing formula pasting in technique.

Once again a very explicit referencing form allows for this, and the form is again similar to the referencing strings I am using generally in this thread post.
(I have done 2 different code line variations of the equivalent of your main code line)

( The code is currently written such that Workbook2 needs to be in the same Folder as the File in which the codes are in, which is Workbook1 in my uploaded Files).

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

For the test codes and two Workbooks, this is an arbitrary test data range in Workbook2:
Using Excel 2007 32 bit

Row\Col	D	E	F
2
3		Wb2_E3
4		Wb2_E4
5		Wb2_E5
6

Worksheet: Shite2

A run of either of the three codes will give you this in Workbook1:

Row\Col	A	B	C
1
2		Wb2_E3
3		Wb2_E4
4		Wb2_E5
5

Worksheet: Sh_t1

For code 3, Workbook2 may be open or closed.



I will be happy to give any more detailed explanation on request.


Dave Allen

_.________________-




Ref:
https://www.youtube.com/watch?v=mYXenjpefNU
https://www.excelforum.com/developme...ml#post4669900
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelforum.com/developme...ml#post4618975
https://www.excelforum.com/developme...ml#post4608252


Codes:

Please Login or Register  to view this content.