Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I'm currently trying to teach myself how to write basic macros in Excel 2007, and have worked out most of what I need to do, except for two things. I've used macros in an older version of Excel where the author set up a pop-up to select the file to open, and would like to do the same here, but can't figure out how to do it. I also haven't got access to the old macros to try to reverse-engineer it - can anyone help?
The other problem I have is that the files I'm working on have some (about 20-30) situations where there are 2 lines with the same reference, and I need to add the 2 lines together in a new line, then delete the original 2 lines; is there a way to do this, bearing in mind that the duplicates won't be in the same place in each file that I need to run the macro on?
Thanks!
The first question is a userForm in the VBA window right click on your project and insert a new form. You can now add controls and link your macros to these controls.
bit more info here:
http://www.contextures.com/xluserform01.html
You lost me on the second question. Are you talking about removing duplicates?
Second question can be sorted using a pivot table and summing the values you want based on the reference.
There are probably other ways of doing it too.
Thanks for that - I'll go play with the forms...
The second problem isn't quite removing duplicates; the two lines contain different data and the result I need is to sum each column in a new row, then delete the original 2 lines. I'm not totally sure it's possible, but someone told my boss that it could be done, so now I'm supposed to do it!
There are few ways to do it. I would use a variable that is unique to the value in the two rows then sum them in VBA, delete the original two rows and insert the new one with your variables value.
Or you could create 2 new columns using an if function and test for the same value.
Anyone here using 2010??
anyony looked into power pivot? just done a training course and it looks really good for local reporting 🙂
Slight thread hijack, but on pivot tables in excel 2003: a user clicks a checkbox on an open spreadsheet, but do I use pivot tables to enable data from a worksheet in a closed workbook to be imported to create a new worksheet in the open workbook? If so, why when I go through the pivot table wizard, can I not just select the spreadsheet in the external source and import everything (I don't get an option to select a range of cells anyway)? Cheers.