Copy/Paste Many Sheets of Data using xlDown and Copy PasteSpecial

Question

I am trying to copy a lot of data from many sheets to another and the line: toSheet.Range(Cells(toRow, toCol), Cells(toRow, toCol)).PasteSpecial xlPasteValues keeps failing with "Runtime Error 1004 You can;t paste here b/c copy paste size are not same ... Select just one cell ..."

I don't know how to fix this. The whole point of this is to not "select" anything at all! I am trying to avoid using selections.

Option Explicit
    Sub CopyFastenerMargins()
    Dim StartTime As Double     'track code run time
    Dim secondsElapsed As Double
    StartTime = Timer
    Application.ScreenUpdating = False  'turn off blinking
    Dim nameRange As Range, r As Range, sht As Range
    Dim fromSheet As Worksheet, toSheet As Worksheet, sheetName As String
    Dim fromRow As Long, fromCol As Long, LCID As Variant
    Dim toRow As Long, toCol As Long, rowCount As Long
    Dim FSY As Range, FSYvalue As Double
    Dim FSU As Range, FSUvalue As Double
    Dim analysisType As String, analysisFlag As Integer

    'Set range containing worksheet names to loop thru
    Set nameRange = Worksheets("TOC").Range("A44:A82")
    'Set destination worksheet
    Set toSheet = Sheets("SuperMargins")

    'find data and copy to destination sheet
    'Loop thru sheets
    Dim i As Long
    For i = 1 To 3
        'pickup current sheet name
        sheetName = nameRange(i)
         Set fromSheet = Sheets(sheetName)
        'find starting location (by header) of data and set range
        Set r = fromSheet.Cells.Find(What:="Minimums by LCID", After:=fromSheet.Cells(1, 1), Lookat:=xlWhole, MatchCase:=True)
        Set r = r.Offset(2, -1)
        fromRow = r.Row
        fromCol = r.Column
        'set row column indices on destination sheet
        toCol = 2
        toRow = lastRow(toSheet) + 1 'get last row using function

        'Copy LCID Range
        fromSheet.Activate
        fromSheet.Range(Cells(fromRow, fromCol), Cells(fromRow, fromCol).End(xlDown)).Copy
        toSheet.Activate
**'********************************NEXT LINE THROWS ERROR**
        toSheet.Range(Cells(toRow, toCol), Cells(toRow, toCol)).PasteSpecial xlPasteValues
    Application.ScreenUpdating = True
    secondsElapsed = Round(Timer - StartTime, 2)
    MsgBox ("Done.  Time:  " & secondsElapsed)

    End Sub


    ' function to determine last row of data
    Function lastRow(sht As Worksheet) As Long

        ' source: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba
        With sht
            If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
                lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
            Else
                lastRow = 1
            End If
        End With

    End Function

score 0 · Accepted Answer · answered Jun 15 '17 at 01:03

0

In this line,

fromSheet.Range(Cells(fromRow, fromCol), Cells(fromRow, fromCol).End(xlDown)).Copy

... the xlDown is going all the way to the bottom of the worksheeet. If fromRow was row 2 then this is 1,048,575 rows. If you now go to paste and you are starting where toRow is anything greater than fromRow then you do not have enough rows to receive the full copy.

Change the .Copy line to,

with fromSheet
    .Range(.Cells(fromRow, fromCol), .Cells(.rows.count, fromCol).End(xlUp)).Copy
end with

By looking from the bottom up, you will still get all of your data and it is unlikely that you will run into the same problem (although theoretically possible).

answered Jun 15 '17 at 01:03

xlDown doesn't **always** go all the way to the bottom of the worksheet but it will if the origin cell is the last cell in that column with a value. It is the same as tapping [ctrl]+[down arrow]. – Jun 15 '17 at 01:07
The origin cell is not the last cell and there are no blanks beneath it. I can't use xlUp because then it would include the header. – Saladsamurai Jun 15 '17 at 01:23
1

If `.Cells(fromRow, fromCol)` is the second cell in the column and then you grab everything from there to `.Cells(.rows.count, fromCol).End(xlUp)` you should be OK. It is not going to grab the header unless the header is the **only** value in the entire column. – Jun 15 '17 at 01:24
@ Jeeped - Thank you. I think this would work for me except I do have special cases. I have the case where .Cells(fromRow, fromCol) is not blank but it is **hidden**. I will probably accept as answer. I just need to think about this. Is there any reason why we should use "With fromSheet" etc instead of referencing the range directly as in "fromSheet.Range(.Cells(fromRow, fromCol), .Cells(.rows.count, fromCol).End(xlUp)).Copy" – Saladsamurai Jun 15 '17 at 02:21
1

I added With fromSheet because I was trying to move you away from Select and Activate to provide parent worksheet references and the Cells that define Range need to be qualified. See [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells).. – Jun 15 '17 at 02:25
Thx for reference thread, it's a good one. I unfortunately have sheets where all rows below header are hidden. So using the above gives strange results (copies the header AND the 1st hidden row value). I tried implementing IF 1st row after header NOT BLANK test, but since it is hidden and not blank, this does not work and results in the header plus 1st hidden row being copied. – Saladsamurai Jun 15 '17 at 14:12

Copy/Paste Many Sheets of Data using xlDown and Copy PasteSpecial

1 Answers1