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("Overview").Cells(Rows.Count,
1).End(xlUp)(2)
Next sh

End Sub

Hi Dan

Try this add-in
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"BaggieDan" <(E-Mail Removed)> wrote in message
news:A143EED7-D26B-4E72-B6D3-(E-Mail Removed)...
> 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("Overview").Cells(Rows.Count,
> 1).End(xlUp)(2)
> Next sh
>
> End Sub

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("Overview").Cells(Rows.Count,
> 1).End(xlUp)(2)
> Next sh
>
> End Sub