Hi Don Wow, works just like magic. Still reading it over and over to see how it works. What part of the code says to 'cut' and and also the 'paste' ? For testing my project I would like to drun that code every 5 seconds for a max of 10 times. Like having a macro to 'call ' this macro and then run every 5 second for 10 times only.
Appreciate your excellent help. Thankyou Charlie Harris Downunder in New Zealand On Wed, Oct 19, 2011 at 2:09 AM, dguillett1 <dguille...@gmail.com> wrote: > I changed your “drag” defined name and it now will update the name > properly if you drag to the next available row in the named range. Or, you > may double click on the cell in col F and the f & g row will be copied to > the next available row. > > If you are saying that you want to drag, in say row 13, from col F to col M > and have the data flow to the next available row. You may double click or > drag from f:g to M: & O > > Option Explicit > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > Boolean) > If Target.Column <> 6 Then Exit Sub > Target.Resize(, 2).Cut Cells(Rows.Count, "m").End(xlUp).Offset(1) > End Sub > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim tr As Long > Dim br As Long > If Target.Column <> 13 Or _ > Intersect(Target, Columns("M:N")) Is Nothing Then Exit Sub > If Cells(Rows.Count, "M").End(xlUp).Offset(-2) = "" Then > tr = Cells(9, "m").End(xlDown).Row + 1 > br = Cells(Rows.Count, "M").End(xlUp).Row > Cells(tr, "m").Resize(br - tr, 2).Delete > End If > End Sub > Sub fixit() 'fire if event code stops working > Application.EnableEvents = True > End Sub > > Don Guillett > SalesAid Software > dguille...@gmail.com > > *From:* Cab Boose <swch...@gmail.com> > *Sent:* Tuesday, October 18, 2011 3:06 AM > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Paste into a Named Range > > Hi Don > > Further to your suggestion of using the offset etc. I have included in > attached workbook, but if I drag a number into column A it places it where I > place it and not in next available row. Would you mind checking and see if > I have this set up correctly. When fixed I can use recorder to get code. > > > Thanks and regards > > Charlie Harris > > > On Tue, Oct 18, 2011 at 1:19 PM, Cab Boose <swch...@gmail.com> wrote: > >> >> Hi Don and ChilExcel >> >> Thanks for info. Don like earlier I have not put enough info. I should >> learn from this. >> >> See attached workbook with comments. >> >> Thanks, apologies and regards >> >> Charlie Harris >> On Tue, Oct 18, 2011 at 12:29 PM, dguillett1 <dguille...@gmail.com>wrote: >> >>> If you have a defined name range such as myrng defined using this, >>> then if you drag to col A it will extend the named range to accommodate the >>> new data. zzzz assumes numbers. If numbers in col A, use 999999 or any >>> number greater than you might use >>> =OFFSET(Sheet1!$A$2,0,0,MATCH("zzzzzzzz",Sheet1!$A:$A),2) >>> >>> Don Guillett >>> SalesAid Software >>> dguille...@gmail.com >>> >>> *From:* Cab Boose <swch...@gmail.com> >>> *Sent:* Monday, October 17, 2011 5:57 PM >>> *To:* excel-macros@googlegroups.com >>> *Subject:* $$Excel-Macros$$ Paste into a Named Range >>> >>> Hi >>> >>> I have a 2 cells in a row that I want to copy into a named range (say >>> 'serialNo') also only 2 columns. >>> >>> Do I have to do the vba xcell up routine ? or should I be able to paste >>> into 'serialno' and it will go into next available row. I have tried >>> recording copy/paste, select 'serialno' but not successful. >>> >>> >>> >>> >>> Thanks >>> >>> >>> Charlie Harris >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel