pasting to A1 rather than K29

[izzyq]

One question that I had was in the my case where I am going to now be copying the range K29:t53, how do I get it copy starting at cell A1 in the new sheet? Presently it copies it to cells K29:T53 in the new sheet.

Also, is there a way to copy the informatio to one single new sheet rather than 10-12 sheets?

Here's the code that I am using right now with the updated paths and range.

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:\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(RangeCopy$).Value = _
wkbCopy.Sheets(1).Range(RangeCopy$).Value

wkbCopy.Close
Set wkbCopy = Nothing

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

Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub

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

 

[acron]

When assigning th evalues to copy try something like :

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


A.C

 

[izzyq]

Hey Acron,

I've tried various combinations on this additonal code yet I unfortunately keep getting an "Invalid Qualifier" error on the "RangeCopy$" Variable. Would you be able to shed some light on this one.

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