Subscript out of Range problem Copy Cell from one workbook to anot

[james]

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

 

[Mike H]

Hi,

Untested but try it like this. Note I think it's a bad idea to copy the
unqualified
range("E1")
You would be better qualifying that range along the lines of

Activeworkbook.sheets("Sheet1").range("E1").copy

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

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 Skaria]

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