actbear
10-29-2007, 03:58 AM
I'm trying to create a macro which copies 4 tabs from a protected spreadsheet to a new file, then copies/paste special (values) to remove the formulas in the original sheets. For some reason when I try to select the range to copy I get the above error message - can anybody help me out please?
The code is below.
Thanks
Workbooks.Add
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\PCT Illustration.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Windows("commissioning toolkit macro test.xls").Activate
Sheets("TrustSummary").Select
ActiveSheet.Unprotect Password:="xxxxx"
Sheets("Target").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets("ContractValue").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets("Assumptions").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Select
Sheets("TrustSummary").Activate
Sheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Copy _
Before:=Workbooks("PCT Illustration.xls").Sheets(1)
ActiveWindow.SmallScroll Down:=6
Activeworkbooks.Sheets("Trust Summary").Select
Range("a1:p47").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Target").Select
ActiveWindow.SmallScroll Down:=27
Range(Cells(1, 1), Cells(55, 22)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ContractValue").Select
ActiveWindow.SmallScroll Down:=24
Range("A1:U55").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The code is below.
Thanks
Workbooks.Add
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\PCT Illustration.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Windows("commissioning toolkit macro test.xls").Activate
Sheets("TrustSummary").Select
ActiveSheet.Unprotect Password:="xxxxx"
Sheets("Target").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets("ContractValue").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets("Assumptions").Select
ActiveSheet.Unprotect Password:=" xxxxx"
Sheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Select
Sheets("TrustSummary").Activate
Sheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Copy _
Before:=Workbooks("PCT Illustration.xls").Sheets(1)
ActiveWindow.SmallScroll Down:=6
Activeworkbooks.Sheets("Trust Summary").Select
Range("a1:p47").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Target").Select
ActiveWindow.SmallScroll Down:=27
Range(Cells(1, 1), Cells(55, 22)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ContractValue").Select
ActiveWindow.SmallScroll Down:=24
Range("A1:U55").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False