Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
Dear all, I posted a question some days ago, and received an answer however when I run the suggested code it gives me a "sub script out o range" error message. I include the code for you: Option Explicit Sub DataToReport() Dim wb As Workbook Dim rng As Range Dim lr As Long Windows("file1").Activate With Worksheets("data") lr = .Cells(Rows.Count, "E").End(xlUp).down Set rng = .Range("C6:E" & lr) Set rng = Union(rng, .Range("K6:K" & lr)) Set rng = Union(rng, .Range("M6:M" & lr)) End With Set wb = Workbooks("file2.xls") rng.Copy wb.Worksheets("data2").Range("B484") End Sub The idea is to take columns C,D,E,K,L,M from file1 sheet "data" an copy into file2 sheet "data2". The data in file1 will vary in length but always starts in row6. When it is copied across, it always starts on row 484. The columns are all of equal length, I just cant work out what i wrong. Please help if you can. love Amy xx : -- AmyTaylo ----------------------------------------------------------------------- AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097 View this thread: http://www.excelforum.com/showthread.php?threadid=39010 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
In order for this to work you must have a workbook called file1 open. This
workbook must have a worksheet called data. These are the two most obvious places for you to be getting that error. If you have this workbook open and it contains a sheet called data then let us know which line of code is causing the error. Hope this helps Rowan "AmyTaylor" wrote: Dear all, I posted a question some days ago, and received an answer - however when I run the suggested code it gives me a "sub script out of range" error message. I include the code for you: Option Explicit Sub DataToReport() Dim wb As Workbook Dim rng As Range Dim lr As Long Windows("file1").Activate With Worksheets("data") lr = .Cells(Rows.Count, "E").End(xlUp).down Set rng = .Range("C6:E" & lr) Set rng = Union(rng, .Range("K6:K" & lr)) Set rng = Union(rng, .Range("M6:M" & lr)) End With Set wb = Workbooks("file2.xls") rng.Copy wb.Worksheets("data2").Range("B484") End Sub The idea is to take columns C,D,E,K,L,M from file1 sheet "data" and copy into file2 sheet "data2". The data in file1 will vary in length, but always starts in row6. When it is copied across, it always starts on row 484. The columns are all of equal length, I just cant work out what is wrong. Please help if you can. love Amy xx :) -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=390103 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
Just noticed...you also need to have a workbook called file2 which has a
worksheet called data2 open. "Rowan" wrote: In order for this to work you must have a workbook called file1 open. This workbook must have a worksheet called data. These are the two most obvious places for you to be getting that error. If you have this workbook open and it contains a sheet called data then let us know which line of code is causing the error. Hope this helps Rowan "AmyTaylor" wrote: Dear all, I posted a question some days ago, and received an answer - however when I run the suggested code it gives me a "sub script out of range" error message. I include the code for you: Option Explicit Sub DataToReport() Dim wb As Workbook Dim rng As Range Dim lr As Long Windows("file1").Activate With Worksheets("data") lr = .Cells(Rows.Count, "E").End(xlUp).down Set rng = .Range("C6:E" & lr) Set rng = Union(rng, .Range("K6:K" & lr)) Set rng = Union(rng, .Range("M6:M" & lr)) End With Set wb = Workbooks("file2.xls") rng.Copy wb.Worksheets("data2").Range("B484") End Sub The idea is to take columns C,D,E,K,L,M from file1 sheet "data" and copy into file2 sheet "data2". The data in file1 will vary in length, but always starts in row6. When it is copied across, it always starts on row 484. The columns are all of equal length, I just cant work out what is wrong. Please help if you can. love Amy xx :) -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=390103 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
And one more thing...
you need to change the line lr = .Cells(Rows.Count, "E").End(xlUp).down to lr = .Cells(Rows.Count, "E").End(xlUp).Row "Rowan" wrote: Just noticed...you also need to have a workbook called file2 which has a worksheet called data2 open. "Rowan" wrote: In order for this to work you must have a workbook called file1 open. This workbook must have a worksheet called data. These are the two most obvious places for you to be getting that error. If you have this workbook open and it contains a sheet called data then let us know which line of code is causing the error. Hope this helps Rowan "AmyTaylor" wrote: Dear all, I posted a question some days ago, and received an answer - however when I run the suggested code it gives me a "sub script out of range" error message. I include the code for you: Option Explicit Sub DataToReport() Dim wb As Workbook Dim rng As Range Dim lr As Long Windows("file1").Activate With Worksheets("data") lr = .Cells(Rows.Count, "E").End(xlUp).down Set rng = .Range("C6:E" & lr) Set rng = Union(rng, .Range("K6:K" & lr)) Set rng = Union(rng, .Range("M6:M" & lr)) End With Set wb = Workbooks("file2.xls") rng.Copy wb.Worksheets("data2").Range("B484") End Sub The idea is to take columns C,D,E,K,L,M from file1 sheet "data" and copy into file2 sheet "data2". The data in file1 will vary in length, but always starts in row6. When it is copied across, it always starts on row 484. The columns are all of equal length, I just cant work out what is wrong. Please help if you can. love Amy xx :) -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=390103 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
Thanks Rowan, I made the changes you suggested, and it still gives me run time error. Would it be possible to send you a copy of th spreadsheet. Let me know if this would be acceptable. Alternatively, can you think of any other problems which might b causing it ? I cant tell you where it get stuck, as it just switches to the 2n sheet, and gets stuck at that point ! Sorry if I seem dull, but this is all new to me and I am strugglin with some of the basic concepts! Amy x -- AmyTaylo ----------------------------------------------------------------------- AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097 View this thread: http://www.excelforum.com/showthread.php?threadid=39010 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub script out of range problem
Amy
Sorry I didn't reply sooner - I'm on Australian time. You shouldn't need to send me the file. Run the macro again and when you get the error message click on debug. This should take you to the visual basic editor with the offending line highlighted in yellew. Make a note of this line and then click on the Reset button (blue square on the Toolbar). Copy and post the code again with a note to say which line is causing the problem. Regards Rowan "AmyTaylor" wrote: Thanks Rowan, I made the changes you suggested, and it still gives me a run time error. Would it be possible to send you a copy of the spreadsheet. Let me know if this would be acceptable. Alternatively, can you think of any other problems which might be causing it ? I cant tell you where it get stuck, as it just switches to the 2nd sheet, and gets stuck at that point ! Sorry if I seem dull, but this is all new to me and I am struggling with some of the basic concepts! Amy xx -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=390103 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Script out of range error | Excel Programming | |||
Problem with printing from VB-script in Excel | Excel Programming | |||
Row deleting script problem | Excel Programming | |||
Run Time Error 9, Script Out of Range | Excel Programming | |||
Graph problem with VBA script | Excel Programming |