copying changing range

Hi,
I'm very new to all this and used the record macro function to copy part of
a range of data from one workbook to another using paste values only. Being a
complete beginner I was so pleased when it worked beautifully (my first time
:-) ) but then realised that the range will change each month as more data
added.

Could anyone please suggest a way to edit this macro so that it recognises
the end of the data entry. There are formulae in columns A, E and I-N in the
source workbook

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/02/2006 by Mifty
'
' Keyboard Shortcut: Ctrl+a
'
Range("E3:N10").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\TEMP\My Documents\Destination.xls"
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Many thanks
--

Mifty

untested, bu maybe something like this

Option Explicit
Dim lastrow As Long
Sub Macro1()
lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row

'
' Macro1 Macro
' Macro recorded 25/02/2006 by Mifty
'
' Keyboard Shortcut: Ctrl+a
'

Range("E3:N" & lastrow).Copy
Workbooks.Open _
Filename:="C:\Documents and Settings\TEMP\My Documents\Destination.xls"
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


--


Gary


"Mifty" <Mifty@discussions.microsoft.com> wrote in message
news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
> Hi,
> I'm very new to all this and used the record macro function to copy part of
> a range of data from one workbook to another using paste values only. Being a
> complete beginner I was so pleased when it worked beautifully (my first time
> :-) ) but then realised that the range will change each month as more data
> added.
>
> Could anyone please suggest a way to edit this macro so that it recognises
> the end of the data entry. There are formulae in columns A, E and I-N in the
> source workbook
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 25/02/2006 by Mifty
> '
> ' Keyboard Shortcut: Ctrl+a
> '
> Range("E3:N10").Select
> Selection.Copy
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> Range("A3").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> End Sub
>
> Many thanks
> --
>
> Mifty

Thanks Gary,
Will give it a go and let you know
Cheers
--
Mifty


"Gary Keramidas" wrote:

> untested, bu maybe something like this
>
> Option Explicit
> Dim lastrow As Long
> Sub Macro1()
> lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
>
> '
> ' Macro1 Macro
> ' Macro recorded 25/02/2006 by Mifty
> '
> ' Keyboard Shortcut: Ctrl+a
> '
>
> Range("E3:N" & lastrow).Copy
> Workbooks.Open _
> Filename:="C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> End Sub
>
>
> --
>
>
> Gary
>
>
> "Mifty" <Mifty@discussions.microsoft.com> wrote in message
> news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
> > Hi,
> > I'm very new to all this and used the record macro function to copy part of
> > a range of data from one workbook to another using paste values only. Being a
> > complete beginner I was so pleased when it worked beautifully (my first time
> > :-) ) but then realised that the range will change each month as more data
> > added.
> >
> > Could anyone please suggest a way to edit this macro so that it recognises
> > the end of the data entry. There are formulae in columns A, E and I-N in the
> > source workbook
> >
> > Sub Macro1()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 25/02/2006 by Mifty
> > '
> > ' Keyboard Shortcut: Ctrl+a
> > '
> > Range("E3:N10").Select
> > Selection.Copy
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> > Range("A3").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > End Sub
> >
> > Many thanks
> > --
> >
> > Mifty
>
>
>

Sorry Gary,
I'm really struggling with this. Can you give me more help?
How do I specify the range I want (which is E3 to Nx)
and how do I incorporate your bit into the recorded macro?

I would be very grateful for any help. I know I'm trying to run before I can
walk but I'm hoping that once it is working I can come back to decipher it
and learn that way. Not too presumptuous I hope!
Cheers
--
Mifty


"Mifty" wrote:

> Thanks Gary,
> Will give it a go and let you know
> Cheers
> --
> Mifty
>
>
> "Gary Keramidas" wrote:
>
> > untested, bu maybe something like this
> >
> > Option Explicit
> > Dim lastrow As Long
> > Sub Macro1()
> > lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
> >
> > '
> > ' Macro1 Macro
> > ' Macro recorded 25/02/2006 by Mifty
> > '
> > ' Keyboard Shortcut: Ctrl+a
> > '
> >
> > Range("E3:N" & lastrow).Copy
> > Workbooks.Open _
> > Filename:="C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> > Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> > End Sub
> >
> >
> > --
> >
> >
> > Gary
> >
> >
> > "Mifty" <Mifty@discussions.microsoft.com> wrote in message
> > news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
> > > Hi,
> > > I'm very new to all this and used the record macro function to copy part of
> > > a range of data from one workbook to another using paste values only. Being a
> > > complete beginner I was so pleased when it worked beautifully (my first time
> > > :-) ) but then realised that the range will change each month as more data
> > > added.
> > >
> > > Could anyone please suggest a way to edit this macro so that it recognises
> > > the end of the data entry. There are formulae in columns A, E and I-N in the
> > > source workbook
> > >
> > > Sub Macro1()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 25/02/2006 by Mifty
> > > '
> > > ' Keyboard Shortcut: Ctrl+a
> > > '
> > > Range("E3:N10").Select
> > > Selection.Copy
> > > Workbooks.Open Filename:= _
> > > "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> > > Range("A3").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > End Sub
> > >
> > > Many thanks
> > > --
> > >
> > > Mifty
> >
> >
> >

probably need some more info.

the lastrow variable finds the lastrow in column N. so the copy statement copies
E3:N and whatever the lastrow is.

but not knowing anything else, it's kind of hard to go any further

--


Gary


"Mifty" <Mifty@discussions.microsoft.com> wrote in message
news:065D5E3A-6CA6-4E1B-B0FD-8395FF11FAE4@microsoft.com...
> Sorry Gary,
> I'm really struggling with this. Can you give me more help?
> How do I specify the range I want (which is E3 to Nx)
> and how do I incorporate your bit into the recorded macro?
>
> I would be very grateful for any help. I know I'm trying to run before I can
> walk but I'm hoping that once it is working I can come back to decipher it
> and learn that way. Not too presumptuous I hope!
> Cheers
> --
> Mifty
>
>
> "Mifty" wrote:
>
>> Thanks Gary,
>> Will give it a go and let you know
>> Cheers
>> --
>> Mifty
>>
>>
>> "Gary Keramidas" wrote:
>>
>> > untested, bu maybe something like this
>> >
>> > Option Explicit
>> > Dim lastrow As Long
>> > Sub Macro1()
>> > lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
>> >
>> > '
>> > ' Macro1 Macro
>> > ' Macro recorded 25/02/2006 by Mifty
>> > '
>> > ' Keyboard Shortcut: Ctrl+a
>> > '
>> >
>> > Range("E3:N" & lastrow).Copy
>> > Workbooks.Open _
>> > Filename:="C:\Documents and Settings\TEMP\My
>> > Documents\Destination.xls"
>> > Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
>> > SkipBlanks:=False, Transpose:=False
>> > End Sub
>> >
>> >
>> > --
>> >
>> >
>> > Gary
>> >
>> >
>> > "Mifty" <Mifty@discussions.microsoft.com> wrote in message
>> > news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
>> > > Hi,
>> > > I'm very new to all this and used the record macro function to copy part
>> > > of
>> > > a range of data from one workbook to another using paste values only.
>> > > Being a
>> > > complete beginner I was so pleased when it worked beautifully (my first
>> > > time
>> > > :-) ) but then realised that the range will change each month as more
>> > > data
>> > > added.
>> > >
>> > > Could anyone please suggest a way to edit this macro so that it
>> > > recognises
>> > > the end of the data entry. There are formulae in columns A, E and I-N in
>> > > the
>> > > source workbook
>> > >
>> > > Sub Macro1()
>> > > '
>> > > ' Macro1 Macro
>> > > ' Macro recorded 25/02/2006 by Mifty
>> > > '
>> > > ' Keyboard Shortcut: Ctrl+a
>> > > '
>> > > Range("E3:N10").Select
>> > > Selection.Copy
>> > > Workbooks.Open Filename:= _
>> > > "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
>> > > Range("A3").Select
>> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> > > SkipBlanks _
>> > > :=False, Transpose:=False
>> > > End Sub
>> > >
>> > > Many thanks
>> > > --
>> > >
>> > > Mifty
>> >
>> >
>> >

H Mifty,

This may help. Put some data in column N. Copy and paste this in the sheet
module and then run it.

Dim lastrow As Long
Sub LastRowNo()
lastrow = Worksheets(1).Cells(Rows.Count, "N").End(xlUp).Row
MsgBox lastrow
End If

You should get a message box with a number in it which is the same as the
last row of column N that has a value in it. Lets say the message box
return 26.

Now since lastrow = 26, this line of code from Gary's solution:
Range("E3:N" & lastrow).Copy

is the same as Range("E3:N26").Copy

So, when the length of data in column N changes the the range that is to be
copied changes with it.

HTH
Regards,
Howard


"Mifty" <Mifty@discussions.microsoft.com> wrote in message
news:EB2A7969-A7E0-48B9-B8CD-E3BBF2AEF2A9@microsoft.com...
> Hi,
> I'm very new to all this and used the record macro function to copy part
> of
> a range of data from one workbook to another using paste values only.
> Being a
> complete beginner I was so pleased when it worked beautifully (my first
> time
> :-) ) but then realised that the range will change each month as more data
> added.
>
> Could anyone please suggest a way to edit this macro so that it recognises
> the end of the data entry. There are formulae in columns A, E and I-N in
> the
> source workbook
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 25/02/2006 by Mifty
> '
> ' Keyboard Shortcut: Ctrl+a
> '
> Range("E3:N10").Select
> Selection.Copy
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\TEMP\My Documents\Destination.xls"
> Range("A3").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> End Sub
>
> Many thanks
> --
>
> Mifty