how to make macro work across multiple sheets 2

[alc101ma]

Hi guys, I recorded this super simple macro below to make the paste special function in Excel faster to do. This only seems to work within a workbook, though, and does not work when the source and destination workbooks are different. Any hints on how I could acheive that functionality? Thanks.

Attribute VB_Name = "Module1"
Sub paste_value()
Attribute paste_value.VB_Description = "Macro recorded 5/7/2004 by achan"
Attribute paste_value.VB_ProcData.VB_Invoke_Func = "w\n14"
'
' paste_value Macro
' Macro recorded 5/7/2004 by achan
'
' Keyboard Shortcut: Ctrl+w
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[xlbo]

code must be held in your PERSONAL.xls for it to be transferable across workbooks or stored in an add-in - personally, I'd just put the pastespecial>Values button on the toolbar.....you can do that from View>Toolbars>Customise

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[alc101ma]

Thanks for the tip. I have already added it as a button but it's just not as fast and easy as a keyboard shortcut. I will try adding it to personal.xls. Is this a workbook I have to create somewhere? I have never heard of this before... I am not an Excel master by any means.

 

[xlbo]

It's a workbook that stays hidden but the code in modules is available to all open workbooks. Easiest way to create one is to record a macro and in the dialog box, there is a dropdown which asks you where you want to store the macro. choose Personal Macro workbook and it'll create a personal.xls for you. Clear the recorded sub and copy / paste your "pastespecial" sub, then click on the save icon in the VBE

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question