Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Recording simple macro
I have a workbook with different sheets
I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
#2
|
|||
|
|||
Dave
try: Range("D65536").End(xlUp).Offset(1,0).Select There are better ways to do this without selecting sheets or cells but, for the moment, this should get over the problem. Regards Trevor "Dave" wrote in message ... I have a workbook with different sheets I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
#3
|
|||
|
|||
Dave
please don't post to more than one group ... you just waste everyone's time. You have two identical responses in two different groups Regards Trevor "Dave" wrote in message ... I have a workbook with different sheets I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
#4
|
|||
|
|||
No, I have two different responses to the same problem
for which you don't have an answer. I wanted to reach as many eyes as possible and just because someone is looking at a general excel group it doesn't mean they don't know the answer to more specific problems. If you hadn't been curious you wouldn't have looked and you chose to waste yours and everyone else's time with an uninformative posting that could have been mailed direct Bye "Trevor Shuttleworth" wrote in message ... Dave please don't post to more than one group ... you just waste everyone's time. You have two identical responses in two different groups Regards Trevor "Dave" wrote in message ... I have a workbook with different sheets I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
#5
|
|||
|
|||
Looks like the same answer to me:
Range("D65536").End(xlUp).Offset(1,0).Select To Jill Thomlinson, you said: Thanks for your time and help Dave "Jim Thomlinson" wrote in message ... Change Range("D4").Select to Range("D65536").End(xlUp).Offset(1,0).Select -- HTH... Jim Thomlinson "Dave" wrote: I have a workbook with different sheets I fill in data on the balance sheet "Dave" wrote in message ... No, I have two different responses to the same problem for which you don't have an answer. I wanted to reach as many eyes as possible and just because someone is looking at a general excel group it doesn't mean they don't know the answer to more specific problems. If you hadn't been curious you wouldn't have looked and you chose to waste yours and everyone else's time with an uninformative posting that could have been mailed direct Bye "Trevor Shuttleworth" wrote in message ... Dave please don't post to more than one group ... you just waste everyone's time. You have two identical responses in two different groups Regards Trevor "Dave" wrote in message ... I have a workbook with different sheets I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
#6
|
|||
|
|||
Take it easy, Dave. I've always felt they were a little rough on users down
here. If I were you, I'd head on over to www.vbaexpress.com. Sounds to me like you might be needing some VBA, or at very least some friendly help. There you can upload a workbook (or reasonable sample of your workbook), too. People in all (not just the news) groups get cranky when a person "cross-posts". The reason is that 3 experts could be helping you in 3 different places, so you're technically taking their time away from helping others. But they so seldom explain why you shouldn't cross-post, and when they do, it's usually cranky-ass remarks. Don't take it personal. :) It's one of the reasons I have stayed on open forums for years, and not "come down here" into the newsgroups. I wish you lots of luck with your project, and if you show up at VBA Express, send me a PM (I am Dreamboat) and say hi! ******************* ~Anne Troy www.OfficeArticles.com "Dave" wrote in message ... No, I have two different responses to the same problem for which you don't have an answer. I wanted to reach as many eyes as possible and just because someone is looking at a general excel group it doesn't mean they don't know the answer to more specific problems. If you hadn't been curious you wouldn't have looked and you chose to waste yours and everyone else's time with an uninformative posting that could have been mailed direct Bye "Trevor Shuttleworth" wrote in message ... Dave please don't post to more than one group ... you just waste everyone's time. You have two identical responses in two different groups Regards Trevor "Dave" wrote in message ... I have a workbook with different sheets I fill in data on the balance sheet copy cheques data into sheet2, a list of all cheques & copy invoice data into sheet3, a list of all invoices I have recorded a macro and assigned it to a new button for each of these tasks The only problem is, after pasting the data into sheet 2 or 3 I need to move down to select the next empty cell before finishing so that it is set up ready for the next use of the macro. Recording the macro does this fine the first time but as it specifies the actual cell selected it always returns to the same cell, hence it will only paste into the first two cells. I would like it to move down one cell from which ever cell is selected without specifying the location can anyone tell me how to do this or how to edit my original macro Sub ChequesOut() ' ' ChequesOut Macro ' Macro recorded 14/07/2005 by D L ' ' Selection.Copy Sheets("Cheques").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D4").Select (THIS IS THE PROBLEM) Sheets("Spreadsheet").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 43 End With End Sub Thanks to anyone who may endeavour to help Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro doesn't work but recording it does. | Excel Discussion (Misc queries) | |||
Simple Hyperlink Macro | New Users to Excel | |||
Date macro | Excel Discussion (Misc queries) | |||
New Excel user needs help with simple Macro... | New Users to Excel | |||
Can't get simple macro to run | Excel Worksheet Functions |