From
Range("A71:I71").Copy
to
sh.Range("A71:I71").Copy

"BaggieDan" wrote:

Hi All,

Thought I had posted this last night, but obviously went wrong somewhere, so
apologies if this is the second time I have asked this!

I have a Workbook that allows the user to create a new Worksheet each time
someone goes off sick. They then record all the relevant details on the
worksheet. I now need to create a summary workbook that links and copys over
a certain range of information. The number and name of source worksheets
will vary but the destination will always be called Workbook - Report,
Worksheet - Overview.

I have the following code, begged and borrowed from posts and books so
thanks if you recognise any!. It works in so much as it finds the releveant
source file and opens it. The copy and pasting bit then goes a bit wrong.
It selects the correct Range(A71:I71) but only from the first worksheet, not
all the others.

Any help would be greatly appreciated.

Code :

Dim wbk As Workbook
Dim sh As Worksheet
Dim Sourcewb As Workbook

Set wbk = Workbooks.Open(Filename:="path", UpdateLinks:=True,
Password:="password")
Set Sourcewb = ThisWorkbook

For Each sh In Sourcewb.Worksheets
Range("A71:I71").Copy
Destination:=Workbooks("Report").Worksheets("Overv iew").Cells(Rows.Count,
1).End(xlUp)(2)
Next sh

End Sub