I open a .csv file copy and paste data into a new worksheet in Personal.xlsb workbook. Duplicates are removed and data is rearranged and sorted in Sheet(2) only. I insert column L in Sheet(1) and place vlookup formula to look values from Sheet(2). Please help to understand why data on Sheet(1) are rearranges? The code that does the rearranging & sorting is only applied to the second Sheet.
Thanks,
Lenna
Thanks,
Lenna
Code:
Sub ImportReportDates()
Dim csvFileTwo As Variant
Dim csvBookTwo As Workbook
Dim R As Long, C As Long, X As Long, Index As Long, LastRow As Long, LastCol As Long, FinalRowCount As Long
Dim DataIn As Variant, DataOut As Variant
Const ValuesStartColumn As Long = 5 '(Column E)
Dim ws As Worksheet
csvFileTwo = Application.GetOpenFilename("Text Files (*.csv),(*.csv),,Please select CSV file to open")
If (csvFileTwo <> False) Then
Workbooks.Open csvFileTwo
Set csvBookTwo = ActiveWorkbook
End If
Columns("A:G").Select
Selection.Copy
Windows("PERSONAL.XLSB").Activate
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Vote4Lenna"
ActiveSheet.Paste
'rearrange data
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells.find("*", , xlValues, , xlByColumns, xlPrevious).Column
FinalRowCount = Application.CountA(Columns(ValuesStartColumn).Resize(, Columns.Count - ValuesStartColumn + 1))
DataIn = Range("A1").Resize(LastRow, LastCol)
ReDim DataOut(1 To FinalRowCount, 1 To ValuesStartColumn)
Index = Index + 1
For R = 1 To LastRow
For C = 1 To UBound(DataIn, 2)
If Len(DataIn(R, C)) Then
For X = 1 To 4
DataOut(Index, X) = DataIn(R, X)
Next
DataOut(Index, 5) = DataIn(R, C)
If C > 4 Then Index = Index + 1
End If
Next
Next
Application.ScreenUpdating = False
Columns("F").Resize(, Columns.Count - ValuesStartColumn).Clear
Range("A1").Resize(UBound(DataOut), 5) = DataOut
Application.ScreenUpdating = True
'remove duplicates and sort data
Columns("A:E").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("$A$1:$E$" & FinalRow).RemoveDuplicates Columns:=Array(3, 4, 5), _
Header:=xlYes
Columns("B:B").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range( _
"E2:E" & FinalRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(1).Sort
.SetRange Range("A1:E" & FinalRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Vlookup in sheet(1)
Sheets(1).Select
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1], Vote4Lenna!C4:C5,2,FALSE)"
Range("L1").Copy
Range("L1:L" & FinalRow).Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("L1").Select
ActiveCell.FormulaR1C1 = "ReportDate"
' Range("A1").Select
Columns("L:L").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Vote4Lenna").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("A1").Select
csvBookTwo.Close SaveChanges:=False ' or True
End Sub
Last edited:
