VBA - split semi-colon separated values on specific worksheet

[albytross]

Hi,

I would like code which selects column A on worksheet XYZ, and separates all values.
Values are separated by semi-colons ";".

I would then like to copy the separated values from sheet XYZ and paste them into worksheet ABC, and then delete sheet XYZ.

I can work with existing code to tweak it generally, but still have no idea how to write it.. Can anyone help?

 

[Mario_R]

Hello.
Have you tried the 'Text in Columns' menu in your Excel?... I ask because it works for what you need and without a macro.

 

[albytross]

Hi Mario,

I recorded a macro using that function which works thanks, and managed to get copy and paste working too.

Code only works if the macro is called sequentially with my other macros. Does not work as a standalone macro because i think its not on the correct active sheet?
This was module specific code i got to work in my workbook.. I dont have the skills to make it more efficient.

Sub MagSus_SplitValues()
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("MAG_RAW_TEMP")
ws1.Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
True
ws1.[B1] = Split("Reading1")
End Sub

 

[Mario_R]

Very good research.

However, what I would do is:
- Copy that column A into the sheet that should receive the data,
- Delete the source data, and
- Apply the 'TextToColumns' to that second sheet.