Macro Help

[hemjeseti]

So i have a column called BA - i want to do Text to Columns to column BC - but every time i try to record a macro - it 'records' but when i go to execute it in a workbook i get an error.

i'm curious about a couple of things.
A) how do i record a macro that will be available to any workbook i open
B) can i share a macro?
C) why does the macro i record break
D) am i supposed to know VB to create macros?

I'm desperate to get this knocked out so any feedback would be super helpful!!!

here is what my macro looks like - but i didn't code any of this. shouldn't Range be BA? and i don't see BC - is that the 0, 2 offset?

I'm sorry guys - im sure this is super easy and i'm just lame.

Thank you so much in advance!!


Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Selection.TextToColumns Destination:=ActiveCell.Offset(0, 2).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
Other:=True, OtherChar:=":", 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), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), _
Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array( _
36, 1), Array(37, 1), Array(38, 1)), TrailingMinusNumbers:=True
End Sub

 

[Joe4]

To answer your questions:

A) how do i record a macro that will be available to any workbook i open

If you store your Macro in your Personal Workbook Macro, it will be available to you on that computer anytime you have Excel open.
When you turn on the Macro Recorder, it asks you where you want to store it. If you change it from "This Workbook" to "Personal Macro Workbook", it will be stored there.

B) can i share a macro?

There are various ways. The most common is probably to store it right in the workbook you want to use it in (making it a Macro-Enabled Workbook).
You can also create your own Add-In to store VBA code that you can distribute to people (I have never done that myself).
If you want them to have their own Personal Macro Workbook with your code in it, you can copy and paste the VBA code form your Personal Macro Workbook, and sent it to then in a Text document.
Then they can record their own Macro to a Personal Macro Workbook, stop the recorder, and then copy and paste the code from the text file over top of what they have in their Personal Macro Workbook (via the VB Editor)

C) why does the macro i record break

It looks like you already select column BA BEFORE you started recording. So it shows the range to apply it to as "Selection". That means, what range you have selected when you call this code is what it is going to try to run it on. If you always want it to run on column BA, then change this part:

Selection.TextToColumns

to this:

Columns("BA:BA").TextToColumns

D) am i supposed to know VB to create macros?

You can record simple Macros with the Macro Recorder. But the Macro Recorder is VERY literal, and records your precise actions. If you have dynamic conditions that can change, it probably won't work (as recorder) without a few edits directly to the code.
So, you may be able to record some Macros that you can use "as-is", but most of the time, you will need to make some changes to the code (which means you either need to know a little VBA or request some assistance from people like us!).