Code applied to wrong sheet

Lenna

Active Member
Joined
Jun 25, 2014
Messages
269
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

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:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Lenna

Active Member
Joined
Jun 25, 2014
Messages
269
Here is an example to what happens to data in Sheet(1) after code is executed.

Before code is executed.

Code:
original data:
[TABLE="width: 768"]
<tbody>[TR]
[TD]Patient Id[/TD]
[TD]MR Number[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Category[/TD]
[TD]Included In Mailing[/TD]
[TD]Log Time[/TD]
[TD]Sample Date[/TD]
[TD]Assigned Date[/TD]
[TD]Tested Date[/TD]
[TD]Test Id[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580452[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580453[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580438[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580439[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580440[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580450[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580451[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580454[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580455[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580452[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580453[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580438[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580439[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580440[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580450[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580451[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580454[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580455[/TD]
[/TR]
</tbody>[/TABLE]

After code is executed
Code:
[TABLE="width: 843"]
<tbody>[TR]
[TD]Patient Id[/TD]
[TD]MR Number[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Category[/TD]
[TD]Included In Mailing[/TD]
[TD]Log Time[/TD]
[TD]Sample Date[/TD]
[TD]Assigned Date[/TD]
[TD]Tested Date[/TD]
[TD]Test Id[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580452[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580453[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580438[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580439[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580440[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580450[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580451[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580454[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580455[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580452[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580453[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580438[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580439[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580440[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580450[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580451[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580454[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]4/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD]580455[/TD]
[/TR]
</tbody>[/TABLE]
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it because of this (inter alia)?

Rich (BB code):
With ActiveWorkbook.Worksheets(1).Sort
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top