+ Reply to Thread
Results 1 to 4 of 4

Issues Copying and Pasting (using Copy method or using Cells().Value)

  1. #1
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Issues Copying and Pasting (using Copy method or using Cells().Value)

    I've been struggling with the following code that should be easy.

    Please Login or Register  to view this content.
    It fails at line:
    Please Login or Register  to view this content.
    ...I get an error message using the Range method on the DestSht; however, my locals window shows DestSht as a valid worksheet.

    I also attempted:
    Please Login or Register  to view this content.
    ...without success.

    There must be something I've been missing b/c I've been having similar problems when I help others on the forum and when I attempt to use similar code within my own projects.

    Thanks in advance...
    Last edited by joe31623; 12-29-2015 at 04:23 PM.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Issues Copying and Pasting (using Copy method or using Cells().Value)

    For Each sht In ThisWorkbook.Sheets
    If sht.Name = PasteShtName Then
    Set DestSht = sht
    Exit For
    End If
    Next sht

    I am not sure I understand why you are using this method to set your destination sheet to a variable. I think I would just use
    Please Login or Register  to view this content.
    Then exclude that sheet in the next For Each sht statement like
    Please Login or Register  to view this content.
    But like I said, I don't really understand why you are using that method. But if that method does not execute to set the variable, then you will get an error on the line saying the object varible is not set.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation

    Hi !

    There is no issue with respect of Excel object model and a serious read of VBA inner help !

    DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value =

    This code may work only if the destination worksheet is active ! Why ? 'Cause there is no worksheet referencing Cells !

    A good code whatever is the active worksheet is

    DestSht.Range(DestSht.Cells(copyrow, 1), DestSht.Cells(copyrow, LastCol)).Value = …


    As written in VBA inner help example when using Copy method, its Destination argument needs only the top left cell !

    sht.Range(sht.Cells(RowsToCopy(rowNum), 1), sht.Cells(RowsToCopy(rowNum), LastCol)).Copy DestSht.Cells(copyrow, 1)


    Whatever with Value or Copy, instead of Range use Cells & Resize :

    sht.Cells(RowsToCopy(rowNum), 1).Resize(, LastCol).Copy DestSht.Cells(copyrow, 1)

    TBTO rule : Think, But Think Object !

  4. #4
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Issues Copying and Pasting (using Copy method or using Cells().Value)

    Good point, JLG.

    Marc -- THANK YOU for clearing that up! I wasn't sure how to use Resize and now I know why my Cells method was not working in a lot of code I wrote.
    Last edited by joe31623; 12-30-2015 at 12:15 AM.

+ 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. Replies: 9
    Last Post: 07-27-2013, 01:59 PM
  2. Replies: 0
    Last Post: 05-15-2013, 03:22 AM
  3. Issues copy and pasting dates from Excel to Access
    By srb104 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 03:52 PM
  4. Copy and pasting issues with mouse connected
    By kas106 in forum Excel General
    Replies: 2
    Last Post: 04-11-2012, 04:52 AM
  5. Copying and Pasting Cells
    By JoshF in forum Excel General
    Replies: 3
    Last Post: 01-04-2012, 08:38 PM
  6. Issues with Pasting Cells
    By sarahwilson1000 in forum Excel General
    Replies: 0
    Last Post: 06-22-2011, 02:13 PM
  7. Copying and Copy and Pasting Formula with Dynamic number of rows
    By patelh9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2010, 11:33 PM
  8. Copy method of worksheet class failed - loop copying worksheets
    By lif in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2006, 05:02 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