Array data to new CSV file

[shayanhaque]

Below is my code:

I'm trying to store data into an array, once its done trying to open new sheet and paste array data into new sheet's column A. It opens new sheet but doesn't unload data into new sheet. Please assist.

Sub LoadArray()

Dim TotalRows As Long
Dim x As Integer
Dim i As Integer
Dim zArray As Variant
Dim ws As Worksheet
Dim rng As Range



Dim strArray As Variant


TotalRows = Rows(Rows.Count).End(xlUp).Row
x = WorksheetFunction.CountA(Worksheets(1).Columns(1))

ReDim strArray(x)
For i = 1 To x
strArray(i) = Cells(i, 1).Value
Range("v1").Offset(i - 1, 0).Value = strArray(i)


Next i
Set newbook = Workbooks.Add
With newbook
ActiveSheet.Range("a1").Value = Application.WorksheetFunction.Transpose(strArray)



MsgBox "Loaded " & UBound(strArray) & " items!"
End Sub

 

[Fluff]

How about

Sub shayanhaque()
   Dim Ary As Variant
   Dim NewBk As Workbook
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2
   Range("V1").Resize(UBound(Ary)).Value = Ary
   Set NewBk = Workbooks.Add
   NewBk.Sheets(1).Range("A1").Resize(UBound(Ary)).Value = Ary
   MsgBox "Loaded " & UBound(Ary) & " items!"
End Sub

 

[shayanhaque]

I really appreciate your help. Thank you. Only issue left is how can i save Newbk as .CSV?

 

[Fluff]

Turn on the macro recorder & save the file as a csv, you then have the code.