+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    10

    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.
    Last edited by wahbob; 01-02-2018 at 03:40 PM. Reason: Clarification on conditions

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007
    Posts
    2,857

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

    .
    https://www.ablebits.com/office-addi...heet-workbook/

    http://www.excel-easy.com/examples/e...eferences.html
    Cats. Because People Suck.

    If someone has assisted you in any way, please be respectful to acknowledge it.
    Simply saying "Thank you" will suffice. If you are so moved, adding to their reputation by clicking
    <------ Add Reputation is a very nice gesture.
    When your question has been answered, Please mark the thread as SOLVED.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany > scroll to the right ... there .. Doc.AElstein has a .... reputation beyond dispute
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,515

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

    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

    Last edited by Doc.AElstein; 01-03-2018 at 04:09 AM. Reason: TestEs and other implicitly implied vulgarities
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    10

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

    Perfect, thanks Dave (Alan) - this solution worked great.
    Will mark as solved.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany > scroll to the right ... there .. Doc.AElstein has a .... reputation beyond dispute
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,515

    value copy (not copy & paste). 3 TestEs

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1