PDA

View Full Version : problem with paste to active workbook



tommy1234
09-08-2007, 08:50 AM
Hello
the code i added copy data from a file that is located on the company network.
The problem is that i have a sheet on the workbook that is protected, the code unprotect the sheet and paste data to it and then the problem occur- I get an error that the data couldn't be pasted because i there is a problem to paste data to Activesheet.
What's worng with my code? can someone help me?
Thanks

Dim abc As String
abc = ActiveWorkbook.Name
Windows("master.xls").Activate 'the which locate on the network
Range("ae1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows(abc).Activate
Worksheets("Percentage_calc").Unprotect Password:="TOMMY12"
Sheets("Percentage_calc").Select 'change the selected worksheet
Range("g1").Select
ActiveSheet.Paste '=> place where the error occurs when i press debug
Worksheets("Percentage_calc").Protect Password:="TOMMY12"
Worksheets("Data").Select 'switch back to the original worksheet
'close master workbook
Windows("master.xls").Activate
ActiveWindow.Close
[A9].Select

Norie
09-08-2007, 08:56 AM
Are you 100% sure you are unprotecting the correct sheet and pasting to the correct sheet?

All that activating/selecting is unneeded and actually makes the code hard to understand.

Sub test()
Dim wbThis As Workbook
Dim wbMst As Workbook
Set wbThis = ThisWorkbook
Set wbMst = Workbooks("master.xls")

With wbThis.Worksheets("Percentage_Calc")
.Unprotect Password:="TOMMY12"
wbMst.ActiveSheet.Range("ae1").CurrentRegion.Copy .Range("G1")
.Protect Password:="TOMMY12"
End With
wbMst.Close
End Sub