Hi all,
I have a hidden workbook called PERSONAL.XLSB located in C:\Users\xxxxxx\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB, that way I can store macros in that workbook that will then be available for all other workbooks I open.
I work alot with an ERP system where the export comes as text in the clipboard, separated by "|" (alt gr + <).
What I do today is paste the export in excel and the use TextToColumns, choose | as the deliminator and then finish. I am trying to find a way to use a macro to do this, and assign the shortcut Ctrl+D to it.
When I record the TextToColumns feature I get the below Macro:
That works fine as long as the macro is stored in the same workbook as it gets called on from. However, when i move the macro to my PERSONAL.XLSB workbook it stops working. Instead of converting text to columns it instead just overwrites all cells with whatever is in cell A1.
Do any one of you know how to amend the macro so it works in whatever workbook it is called in?
Thank you very much for your support. Really appreciate it
.
I have a hidden workbook called PERSONAL.XLSB located in C:\Users\xxxxxx\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB, that way I can store macros in that workbook that will then be available for all other workbooks I open.
I work alot with an ERP system where the export comes as text in the clipboard, separated by "|" (alt gr + <).
What I do today is paste the export in excel and the use TextToColumns, choose | as the deliminator and then finish. I am trying to find a way to use a macro to do this, and assign the shortcut Ctrl+D to it.
When I record the TextToColumns feature I get the below Macro:
Code:
Sub TextToColumns()'
' Macro1 Macro
'
'
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
End Sub
That works fine as long as the macro is stored in the same workbook as it gets called on from. However, when i move the macro to my PERSONAL.XLSB workbook it stops working. Instead of converting text to columns it instead just overwrites all cells with whatever is in cell A1.
Do any one of you know how to amend the macro so it works in whatever workbook it is called in?
Thank you very much for your support. Really appreciate it