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.
The Result
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.
gifGuide2Code 
The dev tab is the key not enable by default i think. Vb nice MsgBox something . . .
LikeLike
Yeah, it took me forever just to figure out how to get the Developer thing up in the ribbon. Why–WHY–isn’t that there by default?!? I swear Microsoft is just trying to give me a coronary.
LikeLiked by 1 person
if you have 2 sheets, say SourceSheet and DestniationSheet then this will work,
—————————————————————-
Dim DestinationSheet As Worksheet
Dim SourceSheetAs Worksheet
Set SourceSheet= ActiveWorkbook.Worksheets(“Shee1 name”)
Set SourceSheet= ActiveWorkbook.Worksheets(“Shee2 name”)
DestinationSheet.Cells(Row1,Column1) = SourceSheet.Cells(Row2,Column2)
—————————————————————-
This way you in loops can manipulate the values.
If you don’t wany to do in different sheets then
SourceSheet.Cells(Row1,Column1) = SourceSheet.Cells(Row2,Column2)
Tip, same is applicable on workbooks too.
Dim SourceBook Workbook
Set SourceBook = Application.Workbooks(ActiveWorkbook.Name)
Just do Activations like below.
—————————————————————-
SourceBook.Activate
—————————————————————-
you can use your names from above like ‘GifGuide.xlsm’ and GifGuide_Invoice.xls for Source and Destination.
Hope this helps. I ‘ll have some examples on my blog if you like.
Cheers
curiousjatin
LikeLiked by 1 person
NICE. I’ve seen your blog–very helpful. Keep posting ’em, will you? Not enough VBA bloggers out there.
LikeLike
Line 4
Set SourceSheet= ActiveWorkbook.Worksheets(“Shee2 name”)
Change to
Set DestinationSheet= ActiveWorkbook.Worksheets(“Shee2 name”)
LikeLike
I worked for a company that used a lot of VBA macro worksheets. Good post!
LikeLike
I take it from your use of the past tense, you are no longer there. Congrats on escaping the spreadsheets 🙂
LikeLiked by 1 person