Importing and filtering .CSV files into Excel via VBA

I'd suggest the easiest way to do this is to split it into the individual parts:

1. Import (not sure if you want to automate it or not, but if you do, I've included instructions.)
2. Filter the cells, removing the ":"
3. Copy Cell A1 to end (or move column A, not sure)

Import

Importing is pretty easy, and can be done with something like this:

Sub importCSV(file As String, wsName As String)
    Dim connection As String
    connection = "TEXT;" + file
    With Worksheets(wsName).QueryTables.Add(Connection:=connection, Destination:=Worksheets(wsName).Range("A1"))
        .Name = file
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        ' If you were using a file with some other type of delimeter, you'd set this to false and then that one to true
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Where file is the file (including directory) and wsName is the name of the worksheet you'll be importing to.

Filter

To filter the cells, try this:

Sub filter(wsName As String)
    Dim c As Range
    For Each c In Worksheets(wsName).usedRange.Cells
        c.value = Right(c.value, Len(c.value) - InStr(c.value, ":"))
    Next
End Sub

What this does is it loops through each cell in the usedRange for the given worksheet (where wsName is the name of the worksheet) and sets the object c to it. Then we can simply set c.value to whatever new value we want, in our case, everything to the right of any :.

To do this, we use the Right() function which takes a string and an integer for the length and returns that many characters from the right hand side. To work out how many characters we want, we get the length of the entire string with Len(), and subtract from it the number of characters up to and including the : with InStr().

(InStr() returns the position of the character if it's in there, or 0, which means if the character isn't there, we'll be calling Right() with the length of the string, so it'll just return the full input.)

Copy

Not entirely sure what you were going for here. If you were trying to move the entire column, use:

Sub moveColumnToEnd(wsName As String, colNum As Integer)
    Dim columnCount As Integer
    With Worksheets(wsName)
        columnCount = .UsedRange.Columns.Count

        .Columns(colNum).Cut
        .Columns(columnCount + 1).Insert
    End With
End Sub

Where wsName is the name of the work sheet and colNum is the number of the column you want to move (in your case 1). Hopefully the code itself is pretty self explanatory. If not, just ask.

If you were trying to just copy Cell A1, try this:

Sub copyA1ToEnd(wsName As String)
    Dim columnCount As Integer
    Dim rowCount As Integer
    Dim copyRange As Range
    Dim c As Range

    With Worksheets(wsName)
        columnCount = .UsedRange.Columns.Count
        rowCount = .UsedRange.Rows.Count

        ' What we're doing here is getting the range from the first cell of the last column+1
        '  all the way to the bottom most cell of that column. The Cells() command takes it's
        '  arguments (row, col) not (X, Y) like you'd probably expect.
        Set copyRange = .Range(.Cells(1, columnCount + 1), .Cells(rowCount, columnCount + 1))

        ' Again, like in the filter function, we loop through each cell in the range and set
        '  it to what we want it to be
        For Each c In copyRange.Cells
            c.Value = .Range("A1").Value
        Next
    End With
End Sub

Where wsName is the name of the worksheet. Again, hopefully it's pretty self explanatory.