VBA copying from large spreadsheets

[Heats]

Hi people,

Hoping someone can point me in the right direction. I have some code that opened a second spreadsheet copied a range, closed the spreadsheet and pasted it into original sheet that worked for test files. However I am now trying to use it on the actual larger files and it is coming up with warnings on information on clipboard etc. I know you can disable this but have been trying to do it the following alternate way;

Public wbMain As Workbook
Public k As Integer
Sub first()
Dim pastelocation As Range

Set wbMain = ActiveWorkbook

k = Range("A" & Rows.Count).End(xlUp).Row
pastelocation = Range("A" & k + 1)

Call second
End Sub


Sub second()

Dim rSrc As Range
Dim rDst As Range
Dim wbCopyFrom As Workbook

Set wbCopyFrom = ActiveWorkbook

Set rSrc = wbCopyFrom.Sheets("Data To Copy").Range("A2:R2", Selection.End(xlDown))
Set rDst = wbMain.Sheets("Final Data").Cells("pastelocation").Resize(rSrc.Rows.Count, rSrc.Columns.Count)
rDst = rSrc.Value

End Sub

Above is the relative pieces of code and it looks like my issue is mainly around the pastelocation variable.

The sub is split as I'm looping through many excel files bringing back information from each.

Any help will be greatly appreciated.

 

[RickXL]

Hi,

I have not tried this but I think you will need to pass the Range called pastelocation in to the second sub. Otherwise it will just think it is a character string.

Try this:

Public wbMain As Workbook
Public k As Integer
Sub first()
Dim pastelocation As Range

Set wbMain = ActiveWorkbook

k = Range("A" & Rows.Count).End(xlUp).Row
pastelocation = Range("A" & k + 1)

Call second(pastelocation)
End Sub


Sub second(pastelocation As Range)

Dim rSrc As Range
Dim rDst As Range
Dim wbCopyFrom As Workbook

Set wbCopyFrom = ActiveWorkbook

Set rSrc = wbCopyFrom.Sheets("Data To Copy").Range("A2:R2", Selection.End(xlDown))
Set rDst = wbMain.Sheets("Final Data").Range(pastelocation).Resize(rSrc.Rows.Count, rSrc.Columns.Count)
rDst = rSrc.Value

End Sub

</pre>