Keyboard Shortcuts Excel 2004

Discussion in 'Mac Apps and Mac App Store' started by f.duane, Sep 23, 2007.

  1. f.duane macrumors newbie

    Joined:
    Apr 30, 2007
    #1
    Hi all,

    my wife is wasting lots of time we should spend together copying and pasting data between spreadsheets. She's transposing data massive amounts of data using the mouse and menu bar - quite inefficient that. So I spent the last two hours trying to find, and then to create, a keyboard shortcut to call the Paste Special command with the transpose option - without success. Does anyone here know the answer?

    Hoping for the infinite macrumors wisdom...

    f.duane
     
  2. geofflilley macrumors newbie

    Joined:
    Nov 27, 2007
    #2
    Can do...two part operation

    First part: record a macro of doing the action paste special, transpose. Couple of details on that. Stay tuned.
    Second part: Assign said macro a keyboard shortcut.

    First part is involved, but the good news is, you only have to do it once:
    1. Go to tools, macro, record new macro. Make sure you give it a useful name (like "pasteSpecialTranspose"; can't have spaces in macro names.) The other piece of information you want to make sure to get right is to save the macro in question to the Personal Macro Workbook. That's important.
    2. Copy some stuff. Doesn't really matter. Let's say it's A1:A5.
    3. Click on another sheet in the same book. Choose a place to paste; like, A1.
    4. Right-click, choose "Paste Special," then check the "transpose" box, like you've been doing.
    5. You should see a floating toolbar with two buttons; the first button is a blue square; if you float your mouse over said blue square, it'll let you stop recording, which is what you want to do right about now.
    6. Click on tools, macro, macros. You should see one listed called "pasteSpecialTranspose," or whatever you called it. Click "Edit."
    7. You'll be in the VBA editor (my friend and ally). Your code will probably look like the example below.

    Sub pasteSpecialTranspose()
    '
    ' pasteSpecialTranspose Macro
    '

    '
    Range("A1:A5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub

    If it doesn't look EXACTLY like this, don't sweat it. Keep the line that begins with the word "Sub." Keep the line that begins with the words, "Selection.PasteSpecial." Keep the line that says "End Sub." Lose the rest.

    Once you have that all squared away, click on the green "X" in the upper-left hand corner that looks like the Excel icon; that'll bring you back to Excel.

    Once you're back in there, click on Tools, Macro, Macros. Select your macro, and choose "options." You can then assign it a keyboard shortcut (like CTRL+SHIFT+P, for example.)

    HTH.
    Cheers
    Geoff
     
  3. ricpac macrumors member

    Joined:
    Jan 4, 2008
    #3
    OMG, i just got my Macbook after Xmas and my Office 04 test drive is about to end soon; so i was looking at Office 2008 --- so how would i be able to paste-special-transpose or among other things i would on Windows Excel if the macros feature on Excel for Mac is no longer available on Office 2008?
     
  4. rasruss macrumors newbie

    Joined:
    Jul 8, 2009
    #4
    2008 was a bad year

    I can get close by using "Customize Keyboard" in the Tools menu, but I still have to select the "transpose" option. Can anyone help out the macro-less 2008 users transpose data more easily?
     

Share This Page