Slight correction...

Sub Rectangle6()
Dim wb As Workbook, ws As Worksheet

Set ws = ActiveSheet
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:="J:\Dummy Rates.xls", UpdateLinks:=3)
wb.Sheets("ITS GB").Range("G5") = ws.Range("E1").Value
Application.DisplayAlerts = False
wb.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


--
Jacob


"Jacob Skaria" wrote:

Use the workbook/worksheet object as below...

Sub Rectangle6()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Filename:="J:\Dummy Rates.xls", UpdateLinks:=3)
wb.Sheets("ITS GB").Range("G5") = ws.Range("E1").Value
End Sub

--OR try the below version if you dont want the user to know whats happening
in the background...(and probably update and close the workbook)

Sub Rectangle6()
Dim wb As Workbook, ws As Worksheet

Set ws = ActiveSheet
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:="J:\Dummy Rates.xls", UpdateLinks:=3)
wb.Sheets("ITS GB").Range("G5") = ws.Range("E1").Value
Application.DisplayAlerts = True
wb.Close True
Application.DisplayAlerts = False
Application.ScreenUpdating = True
End Sub


--
Jacob


"james" wrote:

Hi I have a spreadsheet called MasterSheet.xls
In this sheet is a button with the code below attached. On clicking the
button it should copy cell E1 in "Mastersheet.xls" (which it does) and copy
the value into spreadsheet called "Dummy Rates.xls" under Sheet "ITS GB"
However it comes up with an Error saying 'Subscript out of Range'. If I take
out the line 'Sheets("ITS GB").Select' Then the code works but will only
paste in the active sheet.
Any ideas?

Cheers


Sub Rectangle6()
Range("E1").Select
Selection.Copy
Workbooks.Open Filename:= _
"J:\Dummy Rates.xls" _
, UpdateLinks:=3
Sheets("ITS GB").Select
Range("G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub