Copying to cell A1 - Invalid Compiler 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:ComptrolCorp_RepMONTHEND2002Monthly StewardshipOPEX"
Workbook$ = Dir(Path$ & "*01*.xls")

'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), _
Cells(RangeCopy$.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.
This message was edited by izzyq on 2002-03-26 08:05

 

[Colo]

Sorry. I'm not Obi wan, but try this.
Regards,

Sub Folder_Workbooks() 'ReconstructionByColo
Dim wkbCopy As Workbook, path$, Workbook$, RangeCopy$, Sheet%
With Application
.DisplayAlerts = False
.EnableEvents = False
RangeCopy$ = "K29:T53"
'Ithink Need "/"
path$ = "W:ComptrolCorp_RepMONTHEND2002Monthly StewardshipOPEX/"
Workbook$ = Dir(path$ & "*01*.xls")
Do While Not Workbook$ = ""
Sheet% = Sheet% + 1
Set wkbCopy = GetObject(path$ & Workbook$)
'necessary to clarify which sheet is referred to
With wkbCopy.Sheets(1).Range(RangeCopy$)
ThisWorkbook.Sheets(Sheet%).Cells(1, 1) _
.Resize(.Rows.Count, .Columns.Count).Value = .Resize.Value
End With
wkbCopy.Close
Set wkbCopy = Nothing
Workbook$ = Dir
Loop
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub