I have copied a macro from one (master) spreadsheet to another using the code
below. But when I open the spreadsheet which the code has been copied into up
the kbd shortcut which I had given it is lost. The code works if I go alt+F8
| run; and I can re-assign the kbd shortcut through options but I want it to
be there automatically. Is there any way to do this when I copy the code
across?
Thanks, Dean.

'copy macro to validated wookbook
Dim FName As String
With Workbooks("TEAMS_Master.xls")
FName = .Path & "\code.txt"
.VBProject.VBComponents("Module9").Export FName
End With
Workbooks("CBev.xls").VBProject.VBComponents.Import FName

'code I am copying to new workbook
Sub Macro_t()
'
' Macro_t Macro
' Macro recorded 05/04/2007 by Dean
'
' Keyboard Shortcut: Ctrl+t
'
Range("Y2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=25, Criteria1:=">0", Operator:=xlAnd
Range("A1").Select
End Sub

Dean,

try this:

Application.MacroOptions Macro:="Macro_t", ShortcutKey:="t"


--
Hope that helps.

Vergel Adriano


"Dean" wrote:

> I have copied a macro from one (master) spreadsheet to another using the code
> below. But when I open the spreadsheet which the code has been copied into up
> the kbd shortcut which I had given it is lost. The code works if I go alt+F8
> | run; and I can re-assign the kbd shortcut through options but I want it to
> be there automatically. Is there any way to do this when I copy the code
> across?
> Thanks, Dean.
>
> 'copy macro to validated wookbook
> Dim FName As String
> With Workbooks("TEAMS_Master.xls")
> FName = .Path & "\code.txt"
> .VBProject.VBComponents("Module9").Export FName
> End With
> Workbooks("CBev.xls").VBProject.VBComponents.Import FName
>
> 'code I am copying to new workbook
> Sub Macro_t()
> '
> ' Macro_t Macro
> ' Macro recorded 05/04/2007 by Dean
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> Range("Y2").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=25, Criteria1:=">0", Operator:=xlAnd
> Range("A1").Select
> End Sub

Thanks Vergel, that works great.

Cheers, Dean

"Vergel Adriano" wrote:

> Dean,
>
> try this:
>
> Application.MacroOptions Macro:="Macro_t", ShortcutKey:="t"
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Dean" wrote:
>
> > I have copied a macro from one (master) spreadsheet to another using the code
> > below. But when I open the spreadsheet which the code has been copied into up
> > the kbd shortcut which I had given it is lost. The code works if I go alt+F8
> > | run; and I can re-assign the kbd shortcut through options but I want it to
> > be there automatically. Is there any way to do this when I copy the code
> > across?
> > Thanks, Dean.
> >
> > 'copy macro to validated wookbook
> > Dim FName As String
> > With Workbooks("TEAMS_Master.xls")
> > FName = .Path & "\code.txt"
> > .VBProject.VBComponents("Module9").Export FName
> > End With
> > Workbooks("CBev.xls").VBProject.VBComponents.Import FName
> >
> > 'code I am copying to new workbook
> > Sub Macro_t()
> > '
> > ' Macro_t Macro
> > ' Macro recorded 05/04/2007 by Dean
> > '
> > ' Keyboard Shortcut: Ctrl+t
> > '
> > Range("Y2").Select
> > Selection.AutoFilter
> > Selection.AutoFilter Field:=25, Criteria1:=">0", Operator:=xlAnd
> > Range("A1").Select
> > End Sub