Copy Sheets to another workbook

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

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\Flash Report
Files"
Workbooks.Open Filename:= _
"H:\Risk_Management\BarraOne\ConsistancyCheck\Flash 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
 
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
 
Back
Top