VBA: What? Just… What?

whatisthismadness

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.

Not so bad, right?

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.

There it is...

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.

breakdown

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

gif

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.

 

Advertisements
Tagged , , , , , ,

7 thoughts on “VBA: What? Just… What?

  1. ARJ says:

    The dev tab is the key not enable by default i think. Vb nice MsgBox something . . .

    Like

  2. curiousjatin says:

    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

    Liked by 1 person

  3. I worked for a company that used a lot of VBA macro worksheets. Good post!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: