Excel VBA transpose with characters

Question

I have a range with 918 cells (A1:A918). Each cell has one string. I need vba code to surround each cell with quotes (" ") and add a comma (,) at the end. Then Transpose the list. I cannot add those characters to the current list either.

For example

CURRENT LIST  (Sheet1)
Cell A1: Bob
Cell A2: Jane
Cell A3: Dan
Cell A4: Phil
Cell A5: Jimmy


RESULT (Sheet2)
Cell A1: "Bob",
Cell B1: "Jane",
Cell C1: "Dan", 
Cell D1: "Phil",
Cell E1: "Jimmy",

It will appear like this: "Bob", "Jane", "Dan", "Phil", "Jimmy"

I know to use the following to transpose:

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

But I cannot figure out how to include the strings into each cell. Can anyone help?

Answer 1

Can you paste to cell A1 on the destination sheet then use the Text to columns method? http://msdn.microsoft.com/en-us/library/office/ff193593.aspx

Edit: Maybe I didn't understand the question. Try something like

Sub transpose()
Dim rng As Range
Dim ws As Worksheet
Dim last As Range

Set ws = ActiveSheet   
Set last = ws.Cells(Rows.Count, "A").End(xlUp)
Set rng = ws.Range("A1", last)

For Each cell In rng
    Dim hold As String
    hold = """"
    hold = hold + cell.Value
    hold = hold + """" + ", "
    cell.Value = hold
Next cell

rng.Copy
ActiveWorkbook.Sheets(2).Range("A1").PasteSpecial transpose:=True

End Sub

Answer 2

This should do the trick

Sub Macro1()
Worksheets("Sheet1").Range("A1:A6").Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
'Columns("A:A").Select
Sheets("Sheet2").Range("A:A").Select
Selection.NumberFormat = """''""@""''"""","""
Worksheets("Sheet2").Range("A1:A6").Copy
Worksheets("Sheet2").Range("B1").PasteSpecial Transpose:=True
End Sub