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

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

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

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

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

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