B
BaggieDan
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
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