Unanswered: Copying and pasting cells in Excel using vba
Hi - please help.
I've got a whole load of data that are in a spreadsheet. I need to write some code. The data was supplied with organisation name, job title and persons name all down one column. I need to separate it so that they can be in 3 different columns. I've been trying to do this in vba, but can't seem to get it right i.e. copying a range of cells in a row to another range in the same row etc. Can someone please tell me how to do this or suggest a better way of sorting this out?
Excel has a tool for this called 'Text To Columns...' found under the data menu. This parses your text into columns based on a delimiter value you set in the TTColumn Wizard. If you've already determined you need a VB solution for this you need a few basics.
You'll still need to identify a delimiter in order to parse the text out. Could be fixed width, tab, return, comma... this depends on your data.
In VB you can use copy paste actions but this is not a very efficient method. You can refer to the Cell or Range directly and use a variable to identify the row and a varaible or array to contain the parts of your parsed text. That would look something like this: