copy range from one workbook to another 1

[swtrader]

I've copied and made a couple of clunky modifications to internet-sourced code to accomplish:

1. Opening each Excel Workbook in the Tracking Reports folder.
2. Getting a customer number and storing it in schrange (the customer number appears only in Cell E7.
3. Getting a 20 row by 2 column range (dates and balances) and storing it in sourcerange

What I need is to end up with a 20 row by 3 column range such as:
CustomerNo Date Balance (for 20 rows for each customer)

With the code below, what I am getting is complete data populated in
the first row for each customer (CustomerNo, Date, Balance) but the second thru 20th rows show data in Date and Balance columns but the CustomerNo is blank (except for the first row).

????

Thanks.





With Application.FileSearch
.NewSearch
.LookIn = "C:\DATA\Tracking Reports"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
'Workbooks.Open (.FoundFiles(i)), ReadOnly:=True, UpdateLinks:=0
Set mybook = Workbooks.Open(.FoundFiles(i), ReadOnly:=True, UpdateLinks:=xlUpdateLinksNever)
Set sourceRange = mybook.Worksheets(1).Range("D14:E34")
a = sourceRange.Rows.Count
Set schrange = mybook.Worksheets(1).Range("E7:E7") 'THIS IS THE VALUE I'M TRYING TO GET INTO COLUMN A OF THE TARGET WORKSHEET
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, 2). _
Resize(.Rows.Count, .Columns.Count)
End With
With schrange
Set schdestrange = basebook.Worksheets(1).Cells(rnum, 1). _
Resize(.Rows.Count, .Columns.Count)
End With
schdestrange.Value = schrange.Value
destrange.Value = sourceRange.Value

Application.SendKeys ("%{Enter}")
mybook.Close
rnum = i * a + 1

Next i
End If
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

 

[SkipVought]

hi,

I never use resize.

with basebook.Worksheets(1)
     Set destrange = .Range( _
         .Cells(rnum, 2), _
         .Cells(SourceRange.Rows.Count, 2))
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[swtrader]

Thanks, Skip --

This is what I finally scrapped together. Interestingly, I did not use Resize in this part. As is usually the case, I don't fully understand everything but I did get it to work... (The 'syntax' on the Range setting was part of what I had trouble with)

With schrange

Set schdestrange = basebook.Worksheets(1).Range("A" & rnum & ":A" & rnum + 20)
End With
schdestrange.Value = schrange.Value
destrange.Value = sourceRange.Value