Copying a (variable) range from multiple worksheets to a summary worksheet

[kalmir01]

Hello,
I've been having trouble making the below procedure work automatically by just opening all the workbooks I need:
Sub copythenpasteworksheet()
'put active cell in worksheet to be copied
Range("B14:ak14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'paste in report
Windows("cgfd report august 2013 rev1.xlsm").Activate
Sheets("paste raw here").Select
Range("B13").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End sub


Here's a sample code I've modified but couldn't get it working:

Sub WBLoop()

Dim wbk As Workbook, rngToCopy As Range, rngToPaste As Range, sheetname As String, copyrange As Range

sheetname = "CGFD"
Set copyrange = Range("b14:ak14")

With Worksheets("paste raw here")
For Each wbk In Workbooks
'loop through the Open workbooks
If wbk.Name <> ThisWorkbook.Name Then
'exclude this workbook from the Loop
Set rngToPaste = .Range("b13").End(xlDown).Offset(1, 0)
'set the target For the paste
Set rngToCopy = wbk.Sheets(sheetname).Range(copyrange, copyrange.End(xlDown)) 'vba displays error message here
'set the range To be copied
rngToCopy.Copy Destination:=rngToPaste
'do the copying
End If
Next
End With
End Sub

Can you help in correcting the above code? Any help would be much appreciated. Thank you.

kalmir01

[snb]

1. Please use code tags.
2. Describe the start situation as exactly as possible
3. Describe the result situation as exactly as possible

[kalmir01]

Hi,

Sorry if I've rushed in to post without explaining all the details..

Current/start situation: I open different source workbooks and through a macro, automatically copy a range, which varies, in the first worksheet and paste it in a worksheet(paste raw here) in a summary workbook (cgfd report august 2013 rev1.xlsm). In this situation, I must put the cursor in the first worksheet in the opened source workbook everytime I run the macro.

Result situation: I just want to open the different source workbooks and the summary workbook and without putting the cursor in the first worksheet of every source workbook, copy and paste the range in the summary workbook. I tried to do just that in the second code but couldn't get it working. An error occurs in the line of code where I put a remark in red font.

Hope this clears up what the problem and the intended results are. Thanks again for taking time to help.

[snb]

not quite

the name of the folder that contains the source files
the names of the source files.

[kalmir01]

Hi,

Name of the folder that contains the source files: c:\users\2529\Documents\lod
Name of the source files: their name actually varies by date (example:"consolidated tat mmc june 4", "consolidated tat mmc june 3", etc..)