Re: $$Excel-Macros$$ Paste into a Named Range
> > http://tutorialway.com/use-named-range-in-excel/ helplful link -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Paste into a Named Range
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 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 > *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 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 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. assumes numbers. If numbers in col A, use 99 or any >>> number greater than you might use >>> =OFFSET(Sheet1!$A$2,0,0,MATCH("",Sheet1!$A:$A),2) >>> >>> Don Guillett >>> SalesAid Software >>> dguille...@gmail.com >>> >>> *From:* Cab Boose >>> *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 >>> >>> <><><><><><>&l
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 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 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. assumes numbers. If numbers in col A, use 99 or any number >> greater than you might use >> =OFFSET(Sheet1!$A$2,0,0,MATCH("",Sheet1!$A:$A),2) >> >> Don Guillett >> SalesAid Software >> dguille...@gmail.com >> >> *From:* Cab Boose >> *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 PasteToNamedRg.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Paste into a Named Range
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 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. assumes numbers. If numbers in col A, use 99 or any number > greater than you might use > =OFFSET(Sheet1!$A$2,0,0,MATCH("",Sheet1!$A:$A),2) > > Don Guillett > SalesAid Software > dguille...@gmail.com > > *From:* Cab Boose > *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 PasteToNamedRg.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Paste into a Named Range
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. assumes numbers. If numbers in col A, use 99 or any number greater than you might use =OFFSET(Sheet1!$A$2,0,0,MATCH("",Sheet1!$A:$A),2) Don Guillett SalesAid Software dguille...@gmail.com From: Cab Boose 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
Re: $$Excel-Macros$$ Paste into a Named Range
please attach example file 2011/10/17 Cab Boose > 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 > -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- -- 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