Macros Paste Special to another Workbook

[abestkim]

Hi,

I am writing a macros that will work around an already existing WorkbookB that I cannot alter. I am copying a date from WorkbookA, opening
WorkbookB and pasting it. WorkbookB automatically updates its values
throughout (where I'm pasting it the cell is assigned a name report_date
that is based on the entire workbook. WorkbookB pulls values from many
other sources based off this named cell and other formulas). Here's my problem:

For some reason when I have Workbook A and WorkbookB opened and I just run this macros everything runs fine

[vba]Public Sub alreadyopenedreport()
Set FlareReport = Workbooks("Flare report.xls")
'Copy the report date wanted
Range("C2").Select
Selection.Copy
'Open Flare Report
FlareReport.Activate


'Paste date into Flare Report
Sheets("Date").Select
ActiveSheet.Unprotect Password:="PaSsWoRd"
Range("E3").Select
Selection.pastespecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect Password:="PaSsWoRd"
End Sub
[/vba]


But when I try to use this code so that only WorkbookA is opened and the macros will open WorkbookB for me, I am told that there is an error 1004 PasteSpecial method of Range class failed.(The reason I am using the wait 5 minutes is because I'm tryingto let WorkbookB retrieve all the information so it won't freeze on me.)



[vba]Sub transfer_date()
'
'
'
'Copy the report date wanted
Range("C2").Select
Selection.Copy
'Open Flare Report
Workbooks.Open "C:\Documents and Settings\...Flare report.xls"
Application.Wait Now() + TimeValue("00:05:00")
ActiveSheet.Unprotect Password:="PaSsWoRd"
'Paste date into Flare Report
Sheets("Date").Select
Range("E3").Select
Selection.pastespecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveSheet.Protect Password:="PaSsWoRd"
Application.CutCopyMode = False

End Sub[/vba]



Any help and suggestions would be greatly appreciated, I'm self taught
as of very recently and have been working on this for a while to no
avail. Thanks!

[gnod]

it should be in the excel help section

[Tommy]

I moved this thread to the Excel forum where it belongs