I have this code which copies the range K20:T50 from 10 XL files and pastes them to an new work book. Currently it will paste the copied data to K20:T50 in the new sheets and I need it start copying at A1.
The code I have right keeps giving me an "invalid qualifier error" for the "RangeCopy$" variable. The pertinent code is under the "Copy Cell Values" tag.
Sub Folder_WorkbooksCopy()
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(RangeCopy$).Value = _
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
The code I have right keeps giving me an "invalid qualifier error" for the "RangeCopy$" variable. The pertinent code is under the "Copy Cell Values" tag.
Sub Folder_WorkbooksCopy()
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(RangeCopy$).Value = _
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