+ Reply to Thread
Results 1 to 6 of 6

copy mult cells using Range(Cells) format

  1. #1
    Registered User
    Join Date
    04-18-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 365
    Posts
    13

    copy mult cells using Range(Cells) format

    this statement works correctly:
    wbFrom.Sheets(1).Cells(2, 6).Copy Destination:=wbTo.Sheets(strWklySheet).Cells(5, 4)

    this one gives Run-time error 1004 - Application-defined or object-defined error
    wbFrom.Sheets(1).Range(Cells(2, 7), Cells(2, 15)).Copy Destination:=wbTo.Sheets(strWklySheet).Range(Cells(5, 6), Cells(5, 14))

    The fact that the first one works tells me the problem is NOT in the workbook and sheet names, but is apparently tied to defining a multi-cell range in this format. Or, more likely, there's a simple formatting error I'm making that I just can't see.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: copy mult cells using Range(Cells) format

    Cells is an attribute of a Worksheet object. If you do not explicitly qualify it, it will default to the worksheet owning the code, if the code is in a worksheet module, or to the active worksheet, if the code is in a standard module. You are allowing it to default, so the default for Cells has to match the sheet for Range. You are using two different sheets so it's guaranteed to generate an error for one of them.

    This qualifies everything so it will work in any situation:

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 05-16-2020 at 03:18 PM. Reason: Removed stray "With"
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: copy mult cells using Range(Cells) format

    Further to 6StringJazzer's comment when copy/pasting you only need to specify the start cell for the paste range
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-18-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 365
    Posts
    13

    Re: copy mult cells using Range(Cells) format

    Thanks so much to both of you! Works perfectly now.

    I'm living proof of the perils of self-teaching this (and probably other) languages - I get much of it, but the nuances - like what is an attribute of a worksheet object and what that means exactly - are only dimly understood.

    I sincerely appreciate the explanations of why it needs to be (or can be) the way it does as much as - or perhaps more than - the specific correct line of code.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: copy mult cells using Range(Cells) format

    You're welcome & thanks for the feedback.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: copy mult cells using Range(Cells) format

    Quote Originally Posted by Kay9835 View Post
    [...]the nuances - like what is an attribute of a worksheet object and what that means exactly - are only dimly understood.
    VBA is a kinda sorta object-oriented language. It is based on an object model for Excel. Here is a reference for the object models for every Office application.

    The idea is that there are VBA elements that correspond to things in Excel. For example, Worksheet is a VBA class. A particular worksheet is an object that is an instance of the class Worksheet. An object has properties, methods, and objects. A property is a value of that object. For example, a Worksheet has a Name. You can update many of these properties, although some are read-only. A method is a procedure that you can call that will perform some action relative to the object. You can Active a worksheet, for example, which causes it to become the ActiveSheet and also be visible to the user.

    Range is an member object of Worksheet--it's an object, but it only makes sense as part of a Worksheet. Same thing with Cells. When you have a member object like that, you usually need to qualify it with the object that owns it.

    Sheet1.Range("A1").ClearContents

    In some cases, VBA allows a default if you do not qualify it, such as for Workbook and Worksheet. However, if you accept the default, you need to know how the default is determined. In these two examples, the defaults are based on context. (I personally think that the language should not allow qualification defaults for this reason.) Hence my description earlier about how it depends on what kind of module it is in.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Changing color format in cells based on 3 range values in a different column cells.
    By konkonmike in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-08-2019, 08:11 AM
  2. Replies: 0
    Last Post: 05-30-2019, 12:13 PM
  3. [SOLVED] Formula Needed copy/paste data from mult cells to another activated by data ent in a cell
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-09-2017, 11:55 AM
  4. [SOLVED] Formula needed to auto populate in a range of cells on mult sprdsheets based on text input
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-31-2015, 06:40 PM
  5. Copy Format and paste to range of cells
    By Longpod in forum Excel General
    Replies: 4
    Last Post: 06-27-2012, 02:22 AM
  6. Conditional Format:Entry looks to mult. ranges to highlight many cells
    By Mishka1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-30-2007, 07:24 AM
  7. Replies: 2
    Last Post: 08-27-2005, 12:05 PM

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