Excel macros

[John Eakins]

Hi

I have recorded a Macro to import a Text file and then sort it

When I try to use the macro using the Keyboard shortcut the macro stops after importing the file - ie it does not do the sort

However if I step through the Macro using F8 it works OK

Any ideas

Thank you

John

[Bob Phillips]

Not without seeing it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Eakins" <John.Eakins.210a5m_1136202901.0723@excelforum-nospam.com>
wrote in message
news:John.Eakins.210a5m_1136202901.0723@excelforum-nospam.com...
>
> Hi
>
> I have recorded a Macro to import a Text file and then sort it
>
> When I try to use the macro using the Keyboard shortcut the macro stops
> after importing the file - ie it does not do the sort
>
> However if I step through the Macro using F8 it works OK
>
> Any ideas
>
> Thank you
>
> John
>
>
> --
> John Eakins
> ------------------------------------------------------------------------
> John Eakins's Profile:
http://www.excelforum.com/member.php...fo&userid=8325
> View this thread: http://www.excelforum.com/showthread...hreadid=497335
>

[John Eakins]

Hi Bob

Below is the Macro

Thanks
John

Sub GetWB()
'
' GetWB Macro
' Imports WB.ALL recorded 02/01/2006 by johne
'
' Keyboard Shortcut: Ctrl+Shift+W
'
ChDir "F:\JCE"
Workbooks.OpenText Filename:="F:\jce\wb.all", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array( _
18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells.Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(20), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.RemoveSubtotal
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[Dave Peterson]

Drop the shift from the shortcut key combination.

Holding the shift down when opening a workbook tells excel not to run the
auto_open/workbook_open code. It can also confuse excel enough so that it stops
when you think it shouldn't.

John Eakins wrote:
>
> Hi Bob
>
> Below is the Macro
>
> Thanks
> John
>
> Sub GetWB()
> '
> ' GetWB Macro
> ' Imports WB.ALL recorded 02/01/2006 by johne
> '
> ' Keyboard Shortcut: Ctrl+Shift+W
> '
> ChDir "F:\JCE"
> Workbooks.OpenText Filename:="F:\jce\wb.all", Origin:=437,
> StartRow:=1, _
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
> ConsecutiveDelimiter _
> :=False, Tab:=False, Semicolon:=False, Comma:=True,
> Space:=False, _
> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1),
> Array(3, 1), Array(4, 1), _
> Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9,
> 1), Array(10, 1), Array(11, 1), _
> Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1),
> Array(16, 1), Array(17, 1), Array( _
> 18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1),
> Array(23, 1)), _
> TrailingMinusNumbers:=True
> Cells.Select
> Selection.Sort Key1:=Range("D2"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> _
> DataOption1:=xlSortNormal
> Cells.Select
> Selection.Subtotal GroupBy:=4, Function:=xlSum,
> TotalList:=Array(20), _
> Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Selection.RemoveSubtotal
> Cells.Select
> Selection.Sort Key1:=Range("D2"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> _
> DataOption1:=xlSortNormal
>
> --
> John Eakins
> ------------------------------------------------------------------------
> John Eakins's Profile: http://www.excelforum.com/member.php...fo&userid=8325
> View this thread: http://www.excelforum.com/showthread...hreadid=497335

--

Dave Peterson

[John Eakins]

Many Thanks dave - Dropping the shift key sorted the problem