Copying to another workbook

[Martin Wheeler]

xl2003

I am trying to copy data from one workbook to another. What I have so for is
below. VBA does not like Set Wkbk = Workbooks("12Sept05.xls")
and I know I am going to have a problem with copyToRng in that it does not
refer to the workbook or worksheet, just the range.
(Stolen code is just never complete!)

Any help would be greatly appreciated.
Ta,
Martin

Public Sub StartCopy()
Dim wks As Worksheet
Dim Wkbk As Workbooks
Dim Wkbk2 As Workbooks
Dim copyToRng As Range

Set Wkbk = Workbooks("12Sept05.xls")
Set Wkbk2 = Workbooks("TheBigStatTable.xls")

For Each wks In Wkbk
If Left(wks.Name, 1) = "R" Then 'each wkbk has 10 sheets and
I want to copy the data from each putting it at the bottom of the range in
TheBigStatTable
With wks
Set copyToRng = .Range("C12").End(xlDown).Offset(1, 0)
.Range("E3").Copy copyToRng


End With
End If
Next
End Sub

 

[Rowan]

Hi Martin

You need to change the definition of your workbook variables to:

Dim Wkbk As Workbook
Dim Wkbk2 As Workbook

Then to start your loop:
For Each wks In Wkbk.worksheets
'code
next wks

I hope this gets you started
Regards
Rowan

 

[Martin Wheeler]

Hi Rowan,
Thanks thats a great start.
Ta,
Martin