Copying to Cell A1 - Invalid Qualifier error

[izzyq]

I have a peice of code that allows me to copy a range of A29:T53 from an excel file to a cell A1 of a new excel sheet. It continues to give me an Invalid Qualifier Error at the "copy cell values" action.

Here is the code and the error: The invalid qualifier is in red

Option Explicit

Sub Folder_Workbooks()

Dim wkbCopy As Excel.Workbook
Dim Path$, Workbook$, RangeCopy$
Dim Sheet%

Application.DisplayAlerts = False
Application.EnableEvents = False

'set range address to copy from/to
RangeCopy$ = "K29:T53"
Path$ = "W:\Comptrol\Corp_Rep\MONTHEND\2002\Monthly Stewardship\OPEX\"
Workbook$ = Dir(Path$ & "*01*.xls&quot

'loop all workbooks in folder
Do While Not Workbook$ = ""

'assign sheet index to copy data to
Sheet% = Sheet% + 1

'open workbook to copy from
Set wkbCopy = GetObject(Path$ & Workbook$)

'copy cell values
ThisWorkbook.Sheets(Sheet%).Range(Cells(1), _
CellsRangeCopy$.Rows.Count, RangeCopy$.Columns.Count)) _
= wkbCopy.Sheets(1).Range(RangeCopy$).Value

wkbCopy.Close
Set wkbCopy = Nothing

'try to find next workbook in folder
Workbook$ = Dir
Loop

Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub

Any help would be appreciated. Thanks Help Me Obi Wan Kenobi, You're My Only Hope.

 

[acron]

I think you have a problem with

CellsRangeCopy$.Rows.Count

There should be a period seperator between Cells and RangeCopy$,

Cells.RangeCopy$.Rows.Count

A.C.

 

[izzyq]

My apologies acron,
As it stands the code actually looks like this

'copy cell values
ThisWorkbook.Sheets(Sheet%).Range(Cells(1), _
Cells(RangeCopy$.Rows.Count, RangeCopy$.Columns.Count)) _ = wkbCopy.Sheets(1).Range(RangeCopy$).Value

So where you had suggested the period seperator, there is actually a bracket.



Help Me Obi Wan Kenobi, You're My Only Hope.