Set columns datatype in new workbook VBA-CodePudding

I have got a macro like this in which I load data from one worksheet, do stuff with it and then create new workbook in which I save transformed data.

My question is: how do I set the formats (datatypes like date, percentage, int, string, (...)) of entire columns in new workbook? For example I want col1 which is column A to be int, because it is IDs, col2 to be date and col3 percentage for example.

To mention: I save all the data in final workbook starting from third row - 1st row is empty, second has names of columns (headers) and from third to Nth row I have my data.

Sub new_worksheet()

  Dim harm As Worksheet, ids As Worksheet
  Set harm = Sheets("harm   rmk")
  
  Dim wb As Workbook, wb_new As Workbook
  Set wb = ActiveWorkbook
  Set wb_new = Workbooks.Add()
  
  Dim lastRow As Long, arr, arrFin, i As Long, dict As Object
    
  lastRow = harm.Range("A" & harm.Rows.Count).End(xlUp).Row 'last row
  nr_x = harm.Range("F2:H" & lastRow).Value
  arr_ids = harm.Range("L2:L" & lastRow).Value
  
  Set dict = CreateObject("Scripting.Dictionary")           'set the dictionary
  For i = 1 To UBound(arr)
    dict(arr_ids(i, 1)) = Array(nr_x(i, 1), nr_x(i, 2))
    
  Next i
 
  ReDim arrFin(1 To dict.Count, 1 To 3) 'redim the final array to keep two rows per a dictionary key
  For i = 0 To dict.Count - 1
        arrFin(i   1, 1) = dict.Keys()(i)
        arrFin(i   1, 2) = dict.Items()(i)(0)
        arrFin(i   1, 3) = dict.Items()(i)(1)
  Next i
  
    Dim MyArr As Variant
    MyArr = Array("col1", "col2", "col3")
    wb_new.Sheets(1).Range("A2").Resize(1, UBound(MyArr)   1).Value = MyArr
  
    wb_new.Sheets(1).Range("A3").Resize(UBound(arrFin), UBound(arrFin, 2)).Value = arrFin
    wb_new.SaveAs Filename:=wb.Path & "\final_worksheet.xlsm", FileFormat:=52
    wb_new.Close saveChanges:=False
    
    
End Sub

CodePudding user response：

You can set format for an entire column like this:

Columns("A:A").NumberFormat = "0" 'numbers without decimals
Columns("B:B").NumberFormat = "m/d/yyyy" 'dates
Columns("C:C").NumberFormat = "0" 'number
Columns("C:C").Style = "Percent" 'percentages