+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

  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.

    Option Explicit
    
    Sub copydata()
    Const DataShtName As String = "Get Data"
    Const PasteShtName As String = "F-28"
    
    Dim rowNum As Integer
    Dim NumOfXs As Integer
    Dim LastCol As Integer
    Dim copyrow As Integer
    Dim RowsToCopy() As Integer
    
    Dim sht As Worksheet
    Dim DestSht As Worksheet
    NumOfXs = 0
    
    
    
    For Each sht In ThisWorkbook.Sheets
        If sht.Name = PasteShtName Then
            Set DestSht = sht
            Exit For
        End If
    Next sht
    
    For Each sht In ThisWorkbook.Sheets
        If sht.Name = DataShtName Then
            LastCol = sht.Range("A1").End(xlToRight).Column
            
            For rowNum = 2 To sht.Range("A1").End(xlDown).Row
                If Cells(rowNum, 9) = "X" Then
                    ReDim Preserve RowsToCopy(NumOfXs)
                    RowsToCopy(NumOfXs) = rowNum
                    NumOfXs = NumOfXs + 1
                End If
            Next rowNum
    'Note: I use rowNum as RowsToCopy element number instead of rowNum below
            copyrow = 2
            If NumOfXs > 0 Then
                For rowNum = 0 To UBound(RowsToCopy)
                
                    DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value = sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)).Value
                    copyrow = copyrow + 1
                    MsgBox (DestSht.Name)
                Next rowNum
            End If
        End If
        
        Exit For
    Next sht
    
    End Sub
    It fails at line:
    DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value = sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)).Value
    ...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:
    sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)) Copy Destination:= DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol))
    ...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
    Set DestSht = Sheets("F-28")
    Then exclude that sheet in the next For Each sht statement like
    For Each sht In ThisWorkbook.Sheets
    	If sht.Name <> "F-28" Then
    		'And the rest of the code
    	End If
    Next
    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 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.

  4. #4
    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 !

+ 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