Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range problem Copy Cell from one workbook to anot
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range problem Copy Cell from one workbook to anot
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. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range problem Copy Cell from one workbook to anot
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range problem Copy Cell from one workbook to
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep superscript and subscript when copying formula to anot | Excel Discussion (Misc queries) | |||
How can I automatically copy cell contents from one cell into anot | New Users to Excel | |||
Subscript out of range problem | Excel Programming | |||
Subscript out of range problem | Excel Programming | |||
Excel Macro Problem, Add-in need to work in every workbook & Error:9 Subscript out of range | Excel Programming |