Copy and Paste dynamic range between worksheets

Question

I have data from multiple worksheets that need to be aggregated to multiple worksheets based on some source file name. All my data is stored in A16:C115 in the source. I want to aggregate that data into the appropriate worksheet, stacking the data (i.e. set 1- A1:C100, set 2- A101,C200). This data size can be variable between workbooks, so that is why I am using Cells and offsets/rows/col counters.

'dest: name of worksheet for data to be pasted into
'src:  data source worksheet
'offset:  current count of data sources that have been pasted, indexed 0
'rows: row count to be pasted
'col:  column count to be pasted

Sub addTrend(dest As Variant, src As Worksheet, offset As Integer, rows As Integer, col As Integer)

    Debug.Print CStr(dest), offset, rows, col
    src.Range(Cells(16, 1), Cells(15 + rows, col)).Copy (Worksheets(CStr(dest)).Range(Cells((offset * rows) + 1, 1), Cells((offset + 1) * rows, col)))


End Sub

The result of this code is that nothing is pasted into the destination worksheet. Due to reasons below, I am pretty sure the error in my code is in the copy/paste line. As mentioned above, I feel this copy and paste function is written to accomplish the desired behavior, but maybe I am missing something. Any ideas?

Copy/Paste line attempts with no effect:

src.Range(Cells(16, 1), Cells(15 + rows, col)).Copy Worksheets(CStr(dest)).Range("A" & (offset * rows) + 1)

Tested:

I've tested (not shown) that the worksheet dest can be found (iterating all open worksheets and comparing the name). I've tested that the source worksheet is also found (by writing to a cell). The offset is counting correctly, the rows/cols are appropriate.

Here is a snippet of the debug print line:

dapSNVHeight                11             100           3 
dapSNVHeight                12             100           3 
dapSNVHeight                13             100           3 
detAvgPeakHeight             0             100           3 
detAvgPeakHeight             1             100           3 
detAvgPeakHeight             2             100           3

destiation, the current offset, the row count and column count.

Edits/Adds:

Calling loop:

      For Each trendSet In trendSets

            If InStr(ws.name, trendSet) Then
                index = findIndex(setIndex, CStr(trendSet), setSize)
                addTrend trendSet, ws, setCounter(index), 100, 3
                setCounter(index) = setCounter(index) + 1
            End If
      Next trendSet

Also, point that it is the copy/paste function. Calling Debug.Print after the src.range.copy call does not print anything. So maybe the first copy/paste is getting hung.

Resolution:

src.Range(src.Cells(srcRow + 1, 1), src.Cells(srcRow + rows, col)).Copy Worksheets(dest).Range(Worksheets(dest).Cells(cRow + 1, 1), Worksheets(dest).Cells(cRow + rows, col))

@braX yes, the parameter of the copy function. msdn.microsoft.com/en-us/vba/excel-vba/articles/… — Chemistpp
It should be src.Range(src.Cells(16, 1), src.Cells(15 + rows, col)) but that is probably not the cause of your problem. Can you give an example of how you're calling this sub? — SJR
@SJR Perhaps this is impacting. I'm 99% the copy/paste line is causing the problem now. I've added the calling loop. — Chemistpp
@SJR You add that as an answer and I'll select it. But make sure you hit the Worksheets(dest).Cells in the parameters too. You fixed it. — Chemistpp

SJR SJR · Accepted Answer · 2018-02-16T16:11:55

OK, the reason I said it probably wasn't the cause of your problem is that if an issue it causes an RTE 1004 (I think). You need to qualify all ranges with sheet references in case src is not active (that's what causes the error, a range referencing two sheets).

src.Range(src.Cells(16, 1), src.Cells(15 + rows, col)).Copy Worksheets(CStr(dest)).Range("A" & (offset * rows) + 1)

Copy and Paste dynamic range between worksheets

1 Answers