I know shockingly little about Excel. And that is somewhat unsettling because most of my jobs, which I depend on for food and rent and coffee, require a fuck-ton* of spreadsheets.
In the interest of keeping myself rolling in coffee grounds, I’ve made myself sludge through VBA tutorials this weekend and put together a few lines that’ll transfer a cell from one workbook to another.
*Is fuck-ton hyphenated?
Step 1: Get the Developer Tab
You’ll have to go into Options. Then click Customize Ribbon. Then Developer.
Step 2: Add a Button
Go into Insert and select the rectangle under ActiveX Controls. You’ll have to drag a rectangle to make the button appear.
If the Microsoft Visual Basic for Applications window doesn’t open when you click it, select Design Mode on the Developer ribbon and try again.
Step 3: The Code
Excel Easy has a wonderful discussion on Object Hierarchy, which I’d recommend if this part is confusing. Basically though, you gotta be real specific when telling Excel where you want to make changes.
For my test run, all I wanted was to be able to copy a cell from one fake workbook (GifGuide.xlsm) and paste it in another (GifGuide_Invoice.xls). Both these workbooks are already open. No activation required.
This can actually be done in two lines, but there’s gonna be a lot of periods running amok.
I was relieved to find out that you just separate all the pieces with periods. And end with whatever command you want to happen.
A Tip: Keep an eye on that Design Mode button in the Developer ribbon. When it’s selected, clicking the button will show you the code. When it’s deselected, the code will run. If all has gone well, you should see something like the above. I renamed the button to “Transfer to Invoice.”
Ummm… Why is that Useful?
I spend a lot of time copying and pasting from workbook to workbook. The frustrating part is that the columns in each workbook are situated differently. So, no straight copy/pasting rows. I have to sit there and plug in the same cells in a different order.
And since it’s not the most engaging task, I mix up cells constantly. Hence the need for a button that I can program to transfer certain specific cells to go to specific places.
Exciting, I know.
Next week it’s back to Processing or Blender–something with pretty graphics at least.