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

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 wrote:
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

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


"Rowan" wrote in message
...
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 wrote:
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