Forum Discussion
Macro that concatenates two cells, references a Date and then copy pastes value into first
Would love some help writing this macro
i tried attaching a file with more explanations within the file if it helps make it clearer. (in yellow highlights & red text)
I have two sheets. One titled 'Money', the second titled 'Data'
In the 'Money' tab, there's a section which currently begins from row 41 where the macro would be populating the data it's pulling. For clarity's sake, i'll refer to this section as the Expense section.
Essentially, the macro is pulling data from the 'Data' tab based on certain criteria (the date). If the criteria is met, it will copy the data of its respective row from the 'Data' tab; will go to the 'Money' tab, look for the last row that contains data in the first portion of the Expense section, creates a new row and pastes values in this new row; making sure not to be pasting over any previous data
written out as steps:
if the concatenation of N & O of 'Data' sheet, viewed as Date format, matches the month of today's date; then copy and paste values of the concatenation into the first available row in column B of 'Money' sheet (beginning from B42 onwards)
then still referencing that same row in 'Data' sheet, copy columns P thru S and paste values into columns C thru F of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy columns T and U and paste values into columns H & I of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy column V and paste values into column G of 'Money' sheet in the same row that was previously referenced
i hope i explained that decently. please let me know if the Excel file isn't any clearer and i'll add more clarity.
Deleted
Well, as of this writing, your question has 60 views and 0 replies. I'm going to break the pattern. I'm also going to take the risk of trying, perhaps, to explain why nobody has replied.
It looks as if you're trying to create some kind of income and expense tracking workbook. That's admirable. I have one myself that I created. Microsoft even has a very advanced (although also somewhat limited) major product called Money in Excel that you can find by searching here on this website. Money in Excel is a major Excel template; it can draw data from your bank accounts (so long as they're not behind dual factor authentication--the limitation I alluded to in the last sentence).
You are approaching this commendable task--IMHO--in an altogether convoluted way, using brute force (also known as VBA or macro) to accomplish things that Excel can do very elegantly by means of functions calling on a central Table that contains all of your transactions, along with identifiers such as vendor, budget category, etc. There's also the Pivot Table that can create automatically a summary of transactions by category by month (or year); you can click on any cell in the Pivot Table to get a detailed list of the transactions that have gone into the summary number.
In short, Excel can do the heavy lifting for you; you don't need to write macros. But it does require re-thinking the whole design. I'm attaching a very simple example of what I'm describing. Very simple. My own raw data base contains a lot more columns (with identifiers for each row of the credit card (4 of them) or bank account (3 of them); identifiers for budget categories two levels deep)... This example just shows how Excel can take a data table and use the Pivot Table to make sense of the raw data.
thanks a lot for the help mathetes
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power. i created some formulas in here that's i'm pretty proud of, and what i'm trying to do now is likely the most complicated portion of the workbook i'm ultimately trying to create that i hope to use to manage my money, health and investments.
i was hoping to have a macro involved because i wanted to build off it for some of the other things i wanted to incorporate in the file.
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point. i'm thinking over the next couple months if i can create formulas and a macro that helps me do this and considers different dates i'll not just get the file to a place i love, but also feel more comfortable using excel
thanks for sharing your file though, i can use it for some ideas. and thanks again for the input.
Deleted
Some thoughts in response to what you wrote (which I'll quote in italics)
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
I was disappointed in Money in Excel on several points. First, because my bank and stock accounts at Fidelity are all behind dual factor authentication, MiE could not get that data. I had gotten all the credit card data before trying..... But from a sheer design point of view, their budget categories seemed to have been very poorly conceived, and there was only a limited amount of flexibility in modifying them.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power.
Keep at it. You're right about its power. I would just recommend learning to do as much as you can without reverting to macros. The built-in functions are many and varied. Learn to take advantage of them to sort through data. Learn to create tables that reference one another by means of functions like XLOOKUP, INDEX, MATCH, and the like. If you're in the Windows environment, learn Power Query.
Macros are good for recording repetitive actions, and, yes, you might also be able to do some data manipulation. But often I suspect you'll learn later that the data manipulation you can do with a macro is better done, more reliably and faster, with a built in function.
An anecdote to illustrate my point. Years ago (decades ago) I was in charge of the HR database for a major corporation. We had to do a monthly headcount report, summarizing employee counts by division, location, employee category, etc., etc. One of the programmers in IT had written a massive macro that produced the spreadsheet report. It took about 30 minutes to run. I spent some time using nothing but built-in functions and was able to produce accurate results in a small fraction of the time, more reliably. That programmer was simply using the skills she knew and assumed would be more powerful, more suited to the task since it required going through so many records.
That was all on Lotus 1-2-3 and happened in the 1990s..... Excel now is far more powerful than Lotus was then. The data you're working with is far less complicated than what we were working with in tracking transfers, hires, terminations across many thousands of employees. I am suggesting that if you learn how to create well-designed Tables of your transactional data you won't need macros.
FWIW, I have one large workbook that tracks our income and expenses, drawing on data from four credit card companies and three bank accounts. I also have a couple spreadsheets to track investments, one of which is devoted to options trades. No macros are used in any of them. Only built-in functions and tools like Pivot Table. Speaking of which,
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point.
Why "as an absolute last resort"? You write as if it's a very bitter pill, an unpleasant task to be avoided at all costs. Most people, when they discover how easily it can produce useful cross-tabulated summaries of such things as expenses, are delighted. There's a small learning curve, but it's really small. And well worth delving into. Here: this is a website that does a great job of explaining not only the Pivot Table, but many other built-in functions. https://exceljet.net/excel-pivot-tables
I highly recommend that site (and there are others) as something you can make use of in your learning. YouTube also has some excellent videos, some of them by people you'll encounter on these pages.
Deleted
I agree with Mr. mathetes it is not an easy job to create a macros like this. Try to create your report for one date with sufficient extra space and link the date of the report to a list of the dates. Then you can create much more easy macros, thal will change the date in the report, calculate and copy the result to a new sheet or book
I just want to make sure there's no confusion here. You wrote I agree with Mr. @mathetes it is not an easy job to create a macros like this, but in fact we're not in agreement.
I was not saying that it's "not easy" to create a macro....my fundamental point was that creating a macro was the wrong way to approach the task of creating a spreadsheet to track income and expenses. Excel can do wonders without relying on macros, especially in dealing with databases of financial transactions. The Pivot Table is one tool that requires no macros to analyze a database. There are functions like the recently released FILTER that can pull records from one sheet to another based on a variety of criteria.
Macros do have a place. I don't categorically oppose them. But Excel's built-in functions provide reliable ways to accomplish a task like this. It's worth while to spend time learning how to use those built-in functions rather than spending the time to figure out how to write an unnecessary routine to accomplish the same task.
thanks for the input AnaBoyko .
yeah i was thinking last night that i should probably take that route...simplify what i want the macro to do and then just try to evolve it.
