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_Con s").Activate
Application.Scr eenUpdating = False
ChDir "S:\FINANCE\FCS T\2011"
Workbooks.Open Filename:="S:\F INANCE\FCST\201 1\DOS-CONS 2011.xls", UpdateLinks:= _
0
Sheets("Weekly" ).Activate
Cells.Select
Selection.Copy
Workbooks("CHPW EEK Excel 2011.xlsm").Act ivate
Sheets("DOS_Con s").Activate
Range("a1").Sel ect.PasteSpecia l Paste:=xlValues

Workbooks("DOS-CONS 2011.xls").Acti vate
ActiveWindow.Cl ose

'US DOS
Sheets("DOS_US" ).Activate
ChDir "S:\FINANCE\FCS T\2011"
Workbooks.Open Filename:="S:\F INANCE\FCST\201 1\DOS-CHP 2011.xls", UpdateLinks:= _
0
Sheets("Weekly" ).Activate
Cells.Select
Selection.Copy
Workbooks("CHPW EEK Excel 2011.xlsm").Act ivate
Sheets("DOS_US" ).Activate
Range("a1").Sel ect.PasteSpecia l Paste:=xlValues

Workbooks("DOS-CHP 2011.xls").Acti vate
ActiveWindow.Cl ose


'Canada DOS
Sheets("DOS_Can ").Activate
ChDir "S:\FINANCE\FCS T\2011"
Workbooks.Open Filename:="S:\F INANCE\FCST\201 1\DOS-CAN 2011.xls", UpdateLinks:= _
0
Sheets("Weekly" ).Activate
Cells.Select
Selection.Copy
Workbooks("CHPW EEK Excel 2011.xlsm").Act ivate
Sheets("DOS_Can ").Activate
Range("a1").Sel ect.PasteSpecia l Paste:=xlValues

Workbooks("DOS-CAN 2011.xls").Acti vate
ActiveWindow.Cl ose

Application.Ena bleEvents = True
Application.Scr eenUpdating = True
End Sub