Shortcut key for macro problem

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

I have a keyboard shortcut problem with a macro I have
recorded.

The macro opens and formats three text space delimited
files. I know the macro works OK because when I select it
with the mouse in the macro menu and hit Run it works fine.

Now I want to have a keyboard shortcut that will save me
the clicks in the menu. Using Options on the macro menu
allows me to enter a shortcut key (eg ctrl-shift-E).

But when I run the macro using ctrl-shift-E the macro only
works as far as opening the first file. It goes no
further and considers its job done. I don't understand
why this is.

Any ideas?
 
Rafael, here you are, have a look:

Sub eInTransit()

'open the files

Workbooks.OpenText Filename:="C:\INTRANS7.DAT",
Origin:=437, StartRow:=1 _
, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array
(1, 1), Array(2, 1), Array(3 _
, 4)), TrailingMinusNumbers:=True
Workbooks.OpenText Filename:="C:\INTRANS5.DAT",
Origin:=437, StartRow:=1 _
, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array
(1, 1), Array(2, 1), Array(3 _
, 4)), TrailingMinusNumbers:=True
Workbooks.OpenText Filename:="C:\INTRANS1.DAT",
Origin:=437, StartRow:=1 _
, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array
(1, 1), Array(2, 1), Array(3 _
, 4)), TrailingMinusNumbers:=True

'get all three sheets to one file

Workbooks("INTRANS7.DAT").Sheets(1).Move
After:=Workbooks("INTRANS1.DAT").Sheets(1)
Workbooks("INTRANS5.DAT").Sheets(1).Move
Before:=Workbooks("INTRANS1.DAT").Sheets(2)

'put some headers in

Sheets(1).Range("a1").Value = "Code"
Sheets(1).Range("b1").Value = "Qty"
Sheets(1).Range("c1").Value = "Date"
Sheets(2).Range("a1").Value = "Code"
Sheets(2).Range("b1").Value = "Qty"
Sheets(2).Range("c1").Value = "Date"
Sheets(3).Rows("1:1").Delete shift:=xlUp

'sort and subtotal

Sheets(1).Activate
Sheets(1).Range("A1").CurrentRegion.Sort Key1:=Range
("C2"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets(1).Range("a1").CurrentRegion.Subtotal
GroupBy:=3, Function:=xlSum, TotalList:=Array(2)
Sheets(1).Outline.ShowLevels RowLevels:=2
Sheets(2).Activate
Sheets(2).Range("a1").CurrentRegion.Sort Key1:=Range
("C2"), Header:=xlYes
Sheets(2).Range("A1").CurrentRegion.Subtotal
GroupBy:=3, Function:=xlSum, TotalList:=Array(2)
Sheets(2).Outline.ShowLevels RowLevels:=2
Sheets(3).Activate
Sheets(3).Range("a1").CurrentRegion.Sort Key1:=Range
("C2"), Header:=xlYes
Sheets(1).Activate

End Sub
 
Back
Top