Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from Multiple Worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from Multiple Worksheets
Hi Dan
Try this add-in http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "BaggieDan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from Multiple Worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple cells out of multiple worksheets at same time. | Excel Discussion (Misc queries) | |||
Copying multiple worksheets to a template using vb.net | Excel Programming | |||
copying from multiple worksheets | Excel Programming | |||
Copying multiple Worksheets | Excel Discussion (Misc queries) | |||
Copying from multiple worksheets | Excel Discussion (Misc queries) |