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