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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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