How to copy worksheets from one workbook to another?

[Bmayl]

I am an entry level VB person just starting VB classes.

I am attempting to copy worksheets from a closed, password protected workbook, to the active worbook. I want paste value the data into the same worksheet each time, in the past this data was linked. The link have always slowed down the PC performance and have led to many memory issues.

It appears to be worse now that we have switched to Excel 2007.

Here is the code that I attempted to write, it started off working on the first sheet but would not work on multiple sheets. I tweake it and now it is not working.


Sub Data_Import()

' Update DOS Files
' Macro recorded 1/5/2011 by bmayl
Sheets("DOS_Cons").Activate
Application.ScreenUpdating = False
ChDir "S:\FINANCE\FCST\2011"
Workbooks.Open Filename:="S:\FINANCE\FCST\2011\DOS-CONS 2011.xls", UpdateLinks:= _
0
Sheets("Weekly").Activate
Cells.Select
Selection.Copy
Workbooks("CHPWEEK Excel 2011.xlsm").Activate
Sheets("DOS_Cons").Activate
Range("a1").Select.PasteSpecial Paste:=xlValues

Workbooks("DOS-CONS 2011.xls").Activate
ActiveWindow.Close

'US DOS
Sheets("DOS_US").Activate
ChDir "S:\FINANCE\FCST\2011"
Workbooks.Open Filename:="S:\FINANCE\FCST\2011\DOS-CHP 2011.xls", UpdateLinks:= _
0
Sheets("Weekly").Activate
Cells.Select
Selection.Copy
Workbooks("CHPWEEK Excel 2011.xlsm").Activate
Sheets("DOS_US").Activate
Range("a1").Select.PasteSpecial Paste:=xlValues

Workbooks("DOS-CHP 2011.xls").Activate
ActiveWindow.Close


'Canada DOS
Sheets("DOS_Can").Activate
ChDir "S:\FINANCE\FCST\2011"
Workbooks.Open Filename:="S:\FINANCE\FCST\2011\DOS-CAN 2011.xls", UpdateLinks:= _
0
Sheets("Weekly").Activate
Cells.Select
Selection.Copy
Workbooks("CHPWEEK Excel 2011.xlsm").Activate
Sheets("DOS_Can").Activate
Range("a1").Select.PasteSpecial Paste:=xlValues

Workbooks("DOS-CAN 2011.xls").Activate
ActiveWindow.Close

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub