Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi All,
Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob -- Rob Gaffney |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi Rob
Depending upon the version of XL you are running, you may not need to do this. For XL2003 there is a feature called DataLists If you click within your used table range and choose DataList then Excel will create an ever expanding table, where any formulae are automatically created on lines added. This List can be used as the source data for the Pivot Table and it will grow automatically to include the last row used. In XL2007, the feature is further improved and can be found on the Insert tab, as InsertTable. If you are using XL2002 or lower post back and we can provide you with a macro solution. ----- Regards Roger Govier "Gaffnr" wrote in message ... Hi All, Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob -- Rob Gaffney -- ---- Regards Roger Govier |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi Roger
Thanks for your superfast reply. Im not sure I understand yet what you wrote so let me tell you about my end goal to see if what you propose will help. The data is populated via an external source, copied and pasted into Excel. The macro I want is only part of a larger macro. The goal is to put the ssheet on a shared drive so that my organisation can run this independently with their own data. So, they will run the external source report I create. They will copy and paste their data. They will then run a macro from a button in the spreadsheet to copy the formulae down to last row, do some other stuff and create pivot. The next user will repeat and their data could be more or less rows every time. We are using Excel 2003 SP 2. If your first proposal will work, thats sounds great. A little more background would be great. Thanks again Rob -- Rob Gaffney "Roger Govier" wrote: Hi Rob Depending upon the version of XL you are running, you may not need to do this. For XL2003 there is a feature called DataLists If you click within your used table range and choose DataList then Excel will create an ever expanding table, where any formulae are automatically created on lines added. This List can be used as the source data for the Pivot Table and it will grow automatically to include the last row used. In XL2007, the feature is further improved and can be found on the Insert tab, as InsertTable. If you are using XL2002 or lower post back and we can provide you with a macro solution. ----- Regards Roger Govier "Gaffnr" wrote in message ... Hi All, Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob -- Rob Gaffney -- ---- Regards Roger Govier |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi Rob
Yes it will work. Just so you can understand the way it works create a the following on a new sheet A1 name B1 Value1 C1 Value2 D1 Formula1 E1 Formula2 In D1 enter = B1*C1 in E1 enter = D1*E1 Enter some values in A2, B2 and C2 Place cursor in A1, DataListCreate Listclick my data has header rowOK Now on sheet2 enter some appropriate values in A B and C for a few rows. Copy this datamove to Sheet1 and paste the data into A3 (where there should be a blue asterisk) and you will see that the formulae automatically get entered in columns D and E. If there is no asterisk showing in the next available row of the table in Sheet1, click on column A of the last row entered, and a blue line will surround the whole list, including the first blank line following the data, where the asterisk will appear. Now if you place your cursor in any cell on the list and choose DataPivot TableFinish it will use the List as the source, and it will grow dynamically as more rows of data are added. -- Regards Roger Govier "Gaffnr" wrote in message ... Hi Roger Thanks for your superfast reply. Im not sure I understand yet what you wrote so let me tell you about my end goal to see if what you propose will help. The data is populated via an external source, copied and pasted into Excel. The macro I want is only part of a larger macro. The goal is to put the ssheet on a shared drive so that my organisation can run this independently with their own data. So, they will run the external source report I create. They will copy and paste their data. They will then run a macro from a button in the spreadsheet to copy the formulae down to last row, do some other stuff and create pivot. The next user will repeat and their data could be more or less rows every time. We are using Excel 2003 SP 2. If your first proposal will work, thats sounds great. A little more background would be great. Thanks again Rob -- Rob Gaffney "Roger Govier" wrote: Hi Rob Depending upon the version of XL you are running, you may not need to do this. For XL2003 there is a feature called DataLists If you click within your used table range and choose DataList then Excel will create an ever expanding table, where any formulae are automatically created on lines added. This List can be used as the source data for the Pivot Table and it will grow automatically to include the last row used. In XL2007, the feature is further improved and can be found on the Insert tab, as InsertTable. If you are using XL2002 or lower post back and we can provide you with a macro solution. ----- Regards Roger Govier "Gaffnr" wrote in message ... Hi All, Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob -- Rob Gaffney -- ---- Regards Roger Govier |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi Roger
Ive tested this but it wont for me - let me explain. Your idea works for addendums to a list but not how we will be doing it... Region Spains data may contain 1000 rows. They will paste it in over the top of existing data,starting in row 2. any prior data is not needed. Next, Region Germany data contains 500 rows they paste over the top of the spain data. That leaves 500 rows of Spanish data to remove/ What im getting at is that its not an ongoing list, but changes from region to region and from within that, the rows differ from month to month. So, idea is to have a macro that does : step 1 - clear out all formulae apart from row 2 (remember the last row of formulae could differ every time the macro is run) step 2 - paste in the date from another excel SSheet on the same shared drive step 3 - copy the formulae in row 2 down to the last row Step 4 - do other stuff Step 5 - create the pivot tks re. the list. I never knew of that and take it on board re, other projects Nect region France data contains 2500 rows. Thats 2000 more rows than the last set of data used. -- Rob Gaffney "Roger Govier" wrote: Hi Rob Yes it will work. Just so you can understand the way it works create a the following on a new sheet A1 name B1 Value1 C1 Value2 D1 Formula1 E1 Formula2 In D1 enter = B1*C1 in E1 enter = D1*E1 Enter some values in A2, B2 and C2 Place cursor in A1, DataListCreate Listclick my data has header rowOK Now on sheet2 enter some appropriate values in A B and C for a few rows. Copy this datamove to Sheet1 and paste the data into A3 (where there should be a blue asterisk) and you will see that the formulae automatically get entered in columns D and E. If there is no asterisk showing in the next available row of the table in Sheet1, click on column A of the last row entered, and a blue line will surround the whole list, including the first blank line following the data, where the asterisk will appear. Now if you place your cursor in any cell on the list and choose DataPivot TableFinish it will use the List as the source, and it will grow dynamically as more rows of data are added. -- Regards Roger Govier "Gaffnr" wrote in message ... Hi Roger Thanks for your superfast reply. Im not sure I understand yet what you wrote so let me tell you about my end goal to see if what you propose will help. The data is populated via an external source, copied and pasted into Excel. The macro I want is only part of a larger macro. The goal is to put the ssheet on a shared drive so that my organisation can run this independently with their own data. So, they will run the external source report I create. They will copy and paste their data. They will then run a macro from a button in the spreadsheet to copy the formulae down to last row, do some other stuff and create pivot. The next user will repeat and their data could be more or less rows every time. We are using Excel 2003 SP 2. If your first proposal will work, thats sounds great. A little more background would be great. Thanks again Rob -- Rob Gaffney "Roger Govier" wrote: Hi Rob Depending upon the version of XL you are running, you may not need to do this. For XL2003 there is a feature called DataLists If you click within your used table range and choose DataList then Excel will create an ever expanding table, where any formulae are automatically created on lines added. This List can be used as the source data for the Pivot Table and it will grow automatically to include the last row used. In XL2007, the feature is further improved and can be found on the Insert tab, as InsertTable. If you are using XL2002 or lower post back and we can provide you with a macro solution. ----- Regards Roger Govier "Gaffnr" wrote in message ... Hi All, Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob -- Rob Gaffney -- ---- Regards Roger Govier |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Sub Auto_Fill()
Dim Lrow As Long With ActiveSheet Lrow = Range("A" & Rows.Count).End(xlUp).Row Range("D2:F" & Lrow).FillDown End With End Sub Gord Dibben MS Excel MVP On Wed, 28 Nov 2007 03:46:00 -0800, Gaffnr wrote: Hi All, Question re. macros. Im not a programmer thats writes them but someone who simply records and plays. Got a question about something.... Lets say I have formulae in cells D2, E2 and F2. I have data in A2, B2 and C2 down to A10, B10 and C10. Record Macro. I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows. This is fine when I run the macro where the data is always to row 10. what about if my data in a,B,c goes down to Row a) row 20 - i am left with 10 rows where the formulae does not copy b) row 5 - I have rows of formula not needed (as i pivot table the results this is a pain). What I want it every time i run the macro to copy the formulae down to the last row of data, where everit may be. When i do it manaully, i get the mouse pointer into the bottom righ corner of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a small black cross and i double click to copy the formula down to the last row where there is data in c. This is what I want the macro to achieve. I tried copying this mouse stroke but it always on running goes downt to the same row every time. Thanks rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |