sub script out of range problem

[AmyTaylor]

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 :

 

[Guest]

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

 

[Guest]

Just noticed...you also need to have a workbook called file2 which has a
worksheet called data2 open.

 

[Guest]

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

 

[AmyTaylor]

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

 

[Guest]

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