0

I have written some code that performs a data collation task, but there is an issue as the raw data date format is dd-mm-yyyy. Manually, we can do text to column and change it using delimit date mm-dd-yyyy, the code for which I have shared below. However, it is not functioning properly after testing by step into F8 F8 F8 I saw text to column is not happening.

Code:-

Sub Compiling_Of_Data()


'''' Disbaling the screen update and refreshing for fast processing ''''
Application.ScreenUpdating = False

'''' Declaring the names as variables for all Raw file as (RF) and Main working file as (MWF) and their paths ''''

Dim RF1, RF2, RF3, RF4, RF5, RF6, RF7, RF8, RF9, RF10, RF11, RF12, RF13 As Variant
Dim MWF, MWF1, path, path1 As String

path = "C:\Users\Kunal.Khaire\Desktop\My Daily Task\202 - 203 POD KPI DASHBOARD\"
path1 = "C:\Users\Kunal.Khaire\Desktop\My Daily Task\202 - 203 POD KPI DASHBOARD\Raw Dump\"

'''' Variable assigned ''''

MWF = "1 POD KPI Dashboard -  Template.xlsb"
RF1 = "1_Mastersheet_crosstab.csv"
RF2 = "2_Toggle_Count_crosstab.csv"
RF3 = "3_Agent_Disconnection_crosstab.csv"
RF4 = "4_Quiz_Level_crosstab.csv"
RF5 = "5_Overall_Performance_(2)_crosstab.csv"
RF6 = "6_Overall_Performance_(3)_crosstab.csv"
RF7 = "7_Overall_Performance_(4)_crosstab.csv"
RF8 = "8_Overall_Performance_(5)_crosstab.csv"
RF9 = "9_OB_Calls_not_Tagged_crosstab.csv"
RF10 = "10_LB_Tagged_Dump_crosstab.csv"
RF11 = "11_Call_Not_Answered_crosstab.csv"
RF12 = "12_tNPS_crosstab.csv"
RF13 = "13_Nulceus.csv"

Workbooks.Open (path & MWF)

'''' below code delete old data if any ''''

Sheets("Nucleus Dum 213").Select
Rows("4:1048576").Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Sheets("Mastersheet").Select
Rows("5:1048576").Select
Selection.Delete Shift:=xlUp
Range("A2").Select

Sheets(Array("Tnps Raw", "Quality Dump (2)", "Quality Dump (3)", _
    "Quality Dump (4)", "Quality Dump (5)", "OB Calls not Tagged", _
    "Quiz_Level_crosstab (2)", "Toggling", "Agent Disconnection", _
    "Call Not Answered", "Tagged Dump")).Select
Rows("4:1048576").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Nucleus Dum 213").Select
Range("A1").Select



'''' pasting "TNPS Data" in our working file ''''

Workbooks.Open (path1 & RF12)
Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy


Workbooks(MWF).Activate
Sheets("Tnps Raw").Select
Range("A1").Select
ActiveCell.Offset(1, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.FillDown
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B:B"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True
Range("A1").Select

'

R_Dax
  • 706
  • 3
  • 10
  • 25
Kunal Khaire
  • 3
  • 4
  • Back up! ***IMPORT*** the csv file properly, using either Power Query or the Legacy Wizard, and convert the date at that time, **before** Excel misinterprets it. Also rewrite your code after reading [How to avoid using Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Ron Rosenfeld Jul 16 '21 at 11:40
  • These CSV are downloaded from tableau server as crosstab and i did some R&D and if in "FieldInfo:=Array(1, 3)" instead of 3 if i put 4 its working but thank you for suggestion – Kunal Khaire Jul 20 '21 at 18:51

0 Answers0