PDA

View Full Version : [SOLVED] Run Time Error 1004 Select method of range class failed



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

Bob Phillips
10-29-2007, 04:51 AM
Not tested here, but see if this works




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
Worksheets("TrustSummary").Unprotect Password:="xxxxx"
Worksheets("Target").Unprotect Password:=" xxxxx"
Worksheets("ContractValue").Unprotect Password:=" xxxxx"
Worksheets("Assumptions").Unprotect Password:=" xxxxx"
Worksheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Select
Worksheets("TrustSummary").Activate
Worksheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Copy _
Before:=Workbooks("PCT Illustration.xls").Worksheets(1)
With Worksheets("Trust Summary")
.Range("a147").Value = .Range("a147").Value
End With
With Sheets("Target")
.Range("A1:V22").Value = .Range("A1:V22").Value
End With
With Sheets("ContractValue")
.Range("A1:U55").Value = .Range("A1:U55").Value
End With

actbear
10-29-2007, 05:27 AM
Thanks xld - gets me closer but for some reason it doesn't work fully. The first tab works fine, on the second the code runs through but the formulas are still there, and then the 3rd generates an error 1004 application-defined or object-defined error.
Any other suggestions gratefully revceived!

Bob Phillips
10-29-2007, 05:28 AM
Can you post your workbook?

actbear
10-29-2007, 05:43 AM
I'd love to if it got me a solution but unfortunately not sa it contains sensitive data from outside our organisation.

Bob Phillips
10-29-2007, 05:46 AM
Can't you munge the sensitive data?

actbear
10-29-2007, 07:08 AM
Not sure what you mean by munge - is that a typo? I appreciate your attempts to help.

Bob Phillips
10-29-2007, 07:10 AM
No, by munge I mean obfuscate it, encrypt it, do anything that loses its meaning, so you can post it as non-sensitive.

actbear
10-29-2007, 07:27 AM
OK - I knew I was being bit dim there! The spreadsheet is attached - all the data has been taken out., hence all the error values.
There may be a quicker way of doing this so any thoughts on that are also welcome.
Thanks

Bob Phillips
10-29-2007, 03:27 PM
TRy this




Sub ProcessData()
Dim thisWB As Workbook
Dim newWB As Workbook
Set thisWB = ThisWorkbook
With thisWB
.Worksheets("TrustSummary").Unprotect Password:="xxxxx"
.Worksheets("Target").Unprotect Password:=" xxxxx"
.Worksheets("ContractValue").Unprotect Password:=" xxxxx"
.Worksheets("Assumptions").Unprotect Password:=" xxxxx"
.Worksheets(Array("TrustSummary", "Target", "ContractValue", "Assumptions")).Copy
End With
Set newWB = ActiveWorkbook
ChDir "C:\"
newWB.SaveAs Filename:="C:\PCT Illustration.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
With newWB
.Worksheets("TrustSummary").Cells.Copy
.Worksheets("TrustSummary").Cells.PasteSpecial Paste:=xlPasteValues
.Worksheets("Target").Cells.Copy
.Worksheets("Target").Cells.PasteSpecial Paste:=xlPasteValues
.Worksheets("ContractValue").Cells.Copy
.Worksheets("ContractValue").Cells.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

actbear
10-30-2007, 02:57 AM
xld,

That worked a treat! Many thanks for your help - job done.