Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheets to another workbook
Hello,
I am trying to copy over a range of sheets from one workbook to another but there is an error in my VBA statement. Could someone tell me what is going wrong? 'Copy Trial Balances worksheet ChDir "H:\Risk_Management\BarraOne\ConsistancyCheck\Flas h Report Files" Workbooks.Open Filename:= _ "H:\Risk_Management\BarraOne\ConsistancyCheck\Flas h Report Files\Trials.xls" For Each sh In ThisWorkbook.Sheets If sh.Name < "" Then sh.Copy Selection.Copy After:=Workbooks("ConsistancyTemplate.xls").Sheets ("Summary") Windows("Trials.xls").Activate ActiveWindow.Close Many thanks! Rgds, Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheets to another workbook
Hi Robert
I have changed the code you gave and it seems to work fine (i think!) it would seem this code is to run from the workbook called "ConsistancyTemplate.xls" from your code as you only mention two workbooks and you are opening the other workbook within the code, you are opening the workbook called "Trails.xls" and you want to copy all the sheets in this workbook to the original book. In your code you were copying all the sheets in workbook "ConsistancyTemplate.xls" and pasting them in this workbook again after the sheet named "Summary" as this is not the desired effect i have changed this. You use ChDir in your code to change the directory which i took out as you don't need to change the directory if you are going to give the full path in the workbook open event. I also change the ThisWorkbook to ActiveWorkbook as ThisWorkbook is referring to the workbook that is running the code which results in you copying the sheets to the end of the same workbook. Workbooks.Open Filename:= _ "C:\Risk_Management\BarraOne\" _ & "ConsistancyCheck\Flash Report Files\Trials.xls" For Each sh In ActiveWorkbook.Sheets If sh.Name < "" Then sh.Copy After:=Workbooks("ConsistancyTemplate.xls").Sheets ("Summary") End If Next sh Windows("Trials.xls").Activate ActiveWindow.Close I hope this helps you out Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy all sheets to a new workbook | Setting up and Configuration of Excel | |||
Copy several sheets from one workbook into another at the same tim | Excel Discussion (Misc queries) | |||
copy workbook sheets? | Setting up and Configuration of Excel | |||
Copy 1-4 sheets to a new workbook | Excel Programming | |||
copy/move sheets within workbook | Excel Discussion (Misc queries) |