Create Button to Perform Repetitive Task

[mnm7]

I have a report in which I need to copy information from one file to another. I copy the row or column and then Paste Special the information in my other file selecting Values and Transpose each time. The information in the rows and columns is not consistent; some are longer or shorter than others. A while ago, I created a custom button to Paste Special Values Only, but I don't remember how I did it. I can't write macros so I am not exactly sure how I did it. When I look at the macros that are active in my file, none of them relate to Paste Special Values Only. Is there a way to create a button without creating a macro? If not, how would I write a macro to create Paste Special Values and Transpose? Thanks

 

[gardnertoo]

A good way to start is to turn on the macro recorder, perform the steps, and look at the code that results. You'll find a lot of superfluous code. Althought you probably don't care about the scrolling up and down, left and right as you hunt for the cells you want to copy from or paste to, the recorder doesn't know that and captures them anyway, such as:

ActiveWindow.SmallScroll Down:=45

You'll also find steps that should be combined, especially anytime you selected cells before copying. In VBA you don't have to select a cell to do something to it, and the code runs a lot faster if you don't select:

Range("C8:D19").Select
Selection.Copy

Instead of selecting, then doing something to the selection, you can just do something to a specified cell or cells like this:

Range("C8:D19").Copy

If you can post a sample of the data (fictionalized as required for confidentiality) and the "rules" you manually follow when choosing what data to grab on any particular day, we can probably come up with code to find the right range of data to copy/paste/transpose for you.

 

[mikerickson]

Your PasteValues button may have come from Tools>Customize>Customize Toolbars/Menus adding a PasteValues button to a toolbar.

 

[Mark F]

Sub PasteValuesTranspose()
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        Application.CutCopyMode = False
End Sub

Right Click ANY Sheet Tab in Excel, then View Code

Right Click Paste the Code into a Module in "Personal" Folder in VBA and Rename the Module something like "PasteValuesTranspose"
Click Save in VBA
Close VBA

To make the button, In Excel
Tools,
Customise,
"Commands" Tabs,
Down to Macros,
Left Click and Drag the "Yellow Smile" Custom Buttom to a Toolbar at the top (suggest next to Paste),
Right Click the Yellow Smiley (the one you put on the toolbar)
Assign Macro
Drop the "Macros In" Dropdown to Personal
Select the "PasteValuesTranspose" Macro
Right Click the Yellow Smiley on the Toolbar again
Go down to Name: &Custom Button (And Change the &Custom Button to "PasteValuesTranspose"
Change the button Image if you want to!
Click Close

Should be job done

Give it a go

All the best

Mark

 

[mnm7]

Gentlemen, Thank you for the responses. I appreciate your help. Mark, thanks for laying it out so simply. You have made several of my co-workers very happy!
Thanks to all! Mark