VB Scripting Help on Excel document

I have a process that requires me to run a query from a home grown app that exports the data to an excel file.  Since my database, is formatted a bit differently this requires me to massage this data so I can simply copy and paste the data into my Production metrics database.  I have written some code, and gotten help on this forum, to help clean up, rearrange and sort this data.  I have to pull this data every week from a separate database, massage it into the form I need and then copy and paste it into a Production Metrics excel sheet that I have built locally here.  

 I need is some help with four things with the MIDataCleanup VB script:
 1. Cleaning up my script.  Since I am relatively new to this I know things can be scripted more efficiently.
 2. Current MIDataCleanup script loses data when I run it.  The data from column K "Outage start time" disappears  when it gets to the date and time separation process.
 3. I need to do a clean data process.  You can see in cell N2 that there is a space in the cell and these issues result in formula errors when I copy and past the information from MIDataCleanup file to the Production Metrics file.  I got some help here with a clean data process that was built for the production metrics page, but after using it thought that it would be better to do it on the data from the MIDataCleanup page as I didn't have to run the clean data on the whole excel Production Metrics file.  So I copied the code into the MIDataCleanup script and then tried to adjust it to run on sheet 1 in the MIDataCleanup file.  However, I wasn't successful.  
 4.  Column E requires a True/False value.  The source data reports this a null or Yes.  What I need to do is change this to a True for Yes and False for a null value.  I got this to work, sort of as you can see the results go all the way down to row 33 and I only need that to go to the last row of data.  

 I know this is a lot and may be confusing so my apologies up front.  I have attaced my files for your review.
waslog-MI-Report.xls
MIDataCleanup.xlsm
ProdTraining1-IM-METRICS-T-V01.5.xlsm
Rrave26Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi, first off, can you please try this revised procedure, and then let me know which of the issues are still outstanding.  Basically, by cleaning up the code, I think a few of the issues would have been resolved.  The main part of the cleanup involved removing all .Select operations, and performing the tasks on the cells directly.  It removed just over 50 lines.

Regards,

Rob.

Sub MIDataCleanup()
'
' MIDataCleanup Macro
' Keyboard Shortcut: Ctrl+h

Dim Cell As Range
    intLastRow = Cells(65536, 1).End(xlUp).Row
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").FormulaR1C1 = "Ticket Number"
    For i = 1 To 7
        Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
    Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:S").Cut Destination:=Columns("Q:Q")
    Columns("R:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("W:W").Cut Destination:=Columns("U:U")
    Columns("V:V").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AA:AA").Cut Destination:=Columns("Y:Y")
    Columns("Z:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AE:AE").Cut Destination:=Columns("A:A")
    'Columns("A:A").ColumnWidth = 16
    Columns("AD:AD").Cut Destination:=Columns("C:C")
    'Columns("C:C").ColumnWidth = 15.22
    Columns("AF:AF").Cut Destination:=Columns("D:D")
    Range("E1").FormulaR1C1 = "Reconvene"
    'Columns("D:D").ColumnWidth = 11.67
    'Columns("E:E").ColumnWidth = 9.78
    Columns("AG:AH").Cut Destination:=Columns("F:G")
    'Columns("F:G").ColumnWidth = 6.44
    Columns("AC:AC").Cut Destination:=Columns("H:H")
    Range("H1").FormulaR1C1 = "Services Impacted"
    Call SeparateDateAndTime(Range("I2:I" & intLastRow))
    Call SeparateDateAndTime(Range("K2:K" & intLastRow))
    Call SeparateDateAndTime(Range("M2:M" & intLastRow))
    Call SeparateDateAndTime(Range("O2:O" & intLastRow))
    Call SeparateDateAndTime(Range("Q2:Q" & intLastRow))
    Call SeparateDateAndTime(Range("S2:S" & intLastRow))
    Call SeparateDateAndTime(Range("U2:U" & intLastRow))
    Call SeparateDateAndTime(Range("W2:W" & intLastRow))
    Call SeparateDateAndTime(Range("Y2:Y" & intLastRow))
    Call SeparateDateAndTime(Range("AA2:AA" & intLastRow))
    Columns("AC:AH").Delete Shift:=xlToLeft
    Range("I1").FormulaR1C1 = "Start Date of Incident"
    Range("J1").FormulaR1C1 = "Outage Start Time"
    Range("K1").FormulaR1C1 = "IMT Engaged Date"
    Range("L1").FormulaR1C1 = "IMT Engaged Time"
    Range("M1").FormulaR1C1 = "IMT Managed Start Date"
    Range("N1").FormulaR1C1 = "IMT Managed Start Time"
    Range("O1").FormulaR1C1 = "First Support Team Paged Date"
    Range("P1").FormulaR1C1 = "First Support Team Paged Time"
    Range("Q1").FormulaR1C1 = "Initial IR Sent Date"
    Range("R1").FormulaR1C1 = "Initial IR Sent Time"
    Range("S1").FormulaR1C1 = "Problem Solver Notified Date"
    Range("T1").FormulaR1C1 = "Problem Solver Notified Time"
    Range("U1").FormulaR1C1 = "Succesful Health Check Date"
    Range("V1").FormulaR1C1 = "Succesful Health Check Time"
    Range("W1").FormulaR1C1 = "Service Available Date"
    Range("X1").FormulaR1C1 = "Service Available Time"
    Range("Y1").FormulaR1C1 = "IMT Engagement End Date"
    Range("Z1").FormulaR1C1 = "IMT Engagement End Time"
    Range("AA1").FormulaR1C1 = "IMT Admin Tasks Completed Date"
    Range("AB1").FormulaR1C1 = "IMT Admin Tasks Completed Time"
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").FormulaR1C1 = "Impacted Region"
    Range("G1").FormulaR1C1 = "Initiated Region"
    Range("D2:D" & intLastRow).Replace What:="Yes", Replacement:="True", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("D2:D" & intLastRow).Replace What:="NO", Replacement:="False", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E" & intLastRow).Replace What:="", Replacement:="False", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E" & intLastRow).Replace What:="Yes", Replacement:="True", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    'Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("B2:B" & intLastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A1:GM" & intLastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns.AutoFit
    
    End Sub

Open in new window

0
Ken ButtersCommented:
If I understand what macro you copied ... it was cleanData.
Cleandata macro operates on a worksheet.
You are missing a key line of code for that to work properly.

One of the variables declared in the macro is "WS" which is defined as a worksheet.
in your Production workbook version you have this line of code:

Set WS = Sheets("IM Raw Data")

What this does is tells the macro which Sheet in the workbook you are going to be using.

I don't see that you set "WS" equal to anything in the MIDataCleanup version.  Since WS is not set to anything... it won't ever do anything.

You need to set it equal to the name of the sheet you want to clean.

in this case... you need the equivalent line of code as :

Set WS = Sheets("Sheet1")

Sub CleanData()
Dim WS As Worksheet
Dim cCol As Long
Dim cRow As Long
Dim Rng As Range, rCol As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set WS = Sheets("Sheet1")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column

For Each rCol In WS.UsedRange.EntireColumn
    If Application.WorksheetFunction.IsText(rCol.Cells.End(xlDown).Value) And _
     Not Application.WorksheetFunction.IsNumber(rCol.Cells.End(xlDown).Value) And _
     Not IsDate(rCol.Cells.End(xlDown).Value) And _
     Not rCol.Cells.End(xlDown).HasFormula Then
        '---> Create Formula in ZZ
        WS.Range("ZZ1:ZZ" & MaxRow).Formula = "=TRIM(" & Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1)"
        WS.Range("ZZ1:ZZ" & MaxRow).Copy
        WS.Range(Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1").PasteSpecial (xlPasteValues)
    End If
Next rCol

WS.Range("ZZ:ZZ").Delete

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

MsgBox "Data cleaned."

End Sub

Open in new window

0
Rrave26Author Commented:
Told you I was a newbie!  LOL.  I will try this tonight and post an answer here either tonight or first thing tomorrow morning.  I am not neglecting you I am just in meeting all day.
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Rrave26Author Commented:
Good morning Rob,
I tried running the code you provided and I get a Compile error, sub or function not defined.  It starts at line 37 of your code.
0
RobSampsonCommented:
Sorry, forgot to mention that I only posted a revised MIDataCleanup routine, and not the SeparateDateAndTime routine, as that had not changed. Please keep your original SeparateDateAndTime routine in the module.

Rob.
0
Rrave26Author Commented:
Ok, I have copied your code Rob into my sheet where the Call SeparateDateAndTime code exists and I still get the Wrong number of arguments or invalid property assignment error.  Here is how the code looks in my sheet now.  
Sub MIDataCleanup()
'
' MIDataCleanup Macro
' Keyboard Shortcut: Ctrl+h

Dim Cell As Range
    intLastRow = Cells(65536, 1).End(xlUp).Row
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").FormulaR1C1 = "Ticket Number"
    For i = 1 To 7
        Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
    Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:S").Cut Destination:=Columns("Q:Q")
    Columns("R:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("W:W").Cut Destination:=Columns("U:U")
    Columns("V:V").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AA:AA").Cut Destination:=Columns("Y:Y")
    Columns("Z:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AE:AE").Cut Destination:=Columns("A:A")
    'Columns("A:A").ColumnWidth = 16
    Columns("AD:AD").Cut Destination:=Columns("C:C")
    'Columns("C:C").ColumnWidth = 15.22
    Columns("AF:AF").Cut Destination:=Columns("D:D")
    Range("E1").FormulaR1C1 = "Reconvene"
    'Columns("D:D").ColumnWidth = 11.67
    'Columns("E:E").ColumnWidth = 9.78
    Columns("AG:AH").Cut Destination:=Columns("F:G")
    'Columns("F:G").ColumnWidth = 6.44
    Columns("AC:AC").Cut Destination:=Columns("H:H")
    Range("H1").FormulaR1C1 = "Services Impacted"
    Call SeparateDateAndTime(Range("I2:I" & intLastRow))
    Call SeparateDateAndTime(Range("K2:K" & intLastRow))
    Call SeparateDateAndTime(Range("M2:M" & intLastRow))
    Call SeparateDateAndTime(Range("O2:O" & intLastRow))
    Call SeparateDateAndTime(Range("Q2:Q" & intLastRow))
    Call SeparateDateAndTime(Range("S2:S" & intLastRow))
    Call SeparateDateAndTime(Range("U2:U" & intLastRow))
    Call SeparateDateAndTime(Range("W2:W" & intLastRow))
    Call SeparateDateAndTime(Range("Y2:Y" & intLastRow))
    Call SeparateDateAndTime(Range("AA2:AA" & intLastRow))
    Columns("AC:AH").Delete Shift:=xlToLeft
    Range("I1").FormulaR1C1 = "Start Date of Incident"
    Range("J1").FormulaR1C1 = "Outage Start Time"
    Range("K1").FormulaR1C1 = "IMT Engaged Date"
    Range("L1").FormulaR1C1 = "IMT Engaged Time"
    Range("M1").FormulaR1C1 = "IMT Managed Start Date"
    Range("N1").FormulaR1C1 = "IMT Managed Start Time"
    Range("O1").FormulaR1C1 = "First Support Team Paged Date"
    Range("P1").FormulaR1C1 = "First Support Team Paged Time"
    Range("Q1").FormulaR1C1 = "Initial IR Sent Date"
    Range("R1").FormulaR1C1 = "Initial IR Sent Time"
    Range("S1").FormulaR1C1 = "Problem Solver Notified Date"
    Range("T1").FormulaR1C1 = "Problem Solver Notified Time"
    Range("U1").FormulaR1C1 = "Succesful Health Check Date"
    Range("V1").FormulaR1C1 = "Succesful Health Check Time"
    Range("W1").FormulaR1C1 = "Service Available Date"
    Range("X1").FormulaR1C1 = "Service Available Time"
    Range("Y1").FormulaR1C1 = "IMT Engagement End Date"
    Range("Z1").FormulaR1C1 = "IMT Engagement End Time"
    Range("AA1").FormulaR1C1 = "IMT Admin Tasks Completed Date"
    Range("AB1").FormulaR1C1 = "IMT Admin Tasks Completed Time"
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").FormulaR1C1 = "Impacted Region"
    Range("G1").FormulaR1C1 = "Initiated Region"
    Range("D2:D" & intLastRow).Replace What:="Yes", Replacement:="True", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("D2:D" & intLastRow).Replace What:="NO", Replacement:="False", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E" & intLastRow).Replace What:="", Replacement:="False", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2:E" & intLastRow).Replace What:="Yes", Replacement:="True", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    'Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("B2:B" & intLastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A1:GM" & intLastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns.AutoFit
    
    End Sub

Sub SeparateDateAndTime()
For Each Cell In Selection.Cells
        myStr = Cell.Value
        Cell.Value = Mid(myStr, 4, 3) & Left(myStr, 3) & Mid(myStr, 7, 4)
        Cells(Cell.Row, Cell.Column + 1) = Mid(myStr, 12, 5)
        Cell.NumberFormat = "mm/dd/yyyy"
        Cells(Cell.Row, Cell.Column + 1).NumberFormat = "hh:mm"
    Next
End Sub

Sub CleanData()
Dim WS As Worksheet
Dim cCol As Long
Dim cRow As Long
Dim Rng As Range, rCol As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column

For Each rCol In WS.UsedRange.EntireColumn
    If Application.WorksheetFunction.IsText(rCol.Cells.End(xlDown).Value) And _
     Not Application.WorksheetFunction.IsNumber(rCol.Cells.End(xlDown).Value) And _
     Not IsDate(rCol.Cells.End(xlDown).Value) And _
     Not rCol.Cells.End(xlDown).HasFormula Then
        '---> Create Formula in ZZ
        WS.Range("ZZ1:ZZ" & MaxRow).Formula = "=TRIM(" & Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1)"
        WS.Range("ZZ1:ZZ" & MaxRow).Copy
        WS.Range(Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1").PasteSpecial (xlPasteValues)
    End If
Next rCol

WS.Range("ZZ:ZZ").Delete

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

MsgBox "Data cleaned."

End Sub

Open in new window

0
RobSampsonCommented:
Sorry, I forgot I changed that too. Can you change
Sub SeparateDateAndTime()
For Each Cell In Selection.Cells

Open in new window

To
Sub SeparateDateAndTime(TheSelection)
For Each Cell In TheSelection.Cells

Open in new window

0
Rrave26Author Commented:
Rob,
I have made those changes and that seems to work.  But now I am missing the Start Date of Incident and Outage Start time in the code.  I have attached the data download sheet before and after the code run.  I also noticed that cell N2 for example in the MIDataClanup worksheet has a space in the data.  It doesn't seem like my clean code is working.  Any ideas?
waslog-MI-Report.xls
MIDataCleanup.xlsm
0
RobSampsonCommented:
Hi, when I run the MIDataCleanup macro in the MIDataCleanup.xlsm that you originally provided (against the data on that first sheet), I get exactly the same results with my version of the macro.  Am I missing something with the waslog report?  Can you run me through what steps you take to run the macro(s) on the data, and on which sheets it should be run?

Rob.
0
Rrave26Author Commented:
The Waslog report is the data source.  However, the date and times were in the DD/MM/YYYYY format.  My boss, for what ever reason not known to me, wants it in the US standard MM/DD/YYYYY.  So I take the raw data from the WasLog report and I run the script I provided to you in the being.  The output I get is attached as the Waslog MI Report Today, that I just ran and attached for you.  As you can see after I run the script the start date of the incident and the outage start time separates and pulls the data.  I am not sure why this data isn't showing up now.  

Right now this macro is saved as an addin in excel and I run the macro when I download the report from the database.  The file is always named Waslog_MI-Report, then If for some reason I download the file multiple times it adds a 1,2,3,4..... after the report name indicating what version I have run.  I initially created the maro in Module 1, but I have come to find out recently that I don't have a clear understanding of where the macro should be saved, either in the worksheet or in a module.  

I hope this helps.
waslog-MI-Report-Today.xls
0
Ken ButtersCommented:
As far as whether a macro should exist in a module or sheet:

When you do things like select a sheet, or select a cell or change a cell value, these type of activities fire what is called an event.   When you use a sheet, you can add code to execute whenever one of these types events fire.

You can see all the events available for a sheet, by going into VBA editor and selecting a sheet.... then from the dropdown on the main code window Change the selection from "General" to "Worksheet"... then the dropdown box just to the right will show you all the available Events that you can add code to.

In your case, you want your code to run whenever the user chooses to run it, so a module is the correct choice for your code.

I wrote an entirely separate solution for you.  The way it was written in my opinion was way to confusing.  The reason for the confusion is that you started with a single worksheet, and starting moving columns around.  As you do that... the columns for a given action change depending upon where you moved from, and where you moved to, making it kind of a maintenance nightmare.

My solution took a different approach.  I opted to create an entirely new sheet within the workbook.... and then for each column moved the data from the old worksheet to the new worksheet, and then applied whatever changes were necessary to each column, like separating date / times.

After all the data is moved and reformatted on the new sheet, I delete the old sheet, and renamed the new sheet with the old sheet name.  

The net effect is the same.

The attached spreadsheet has the code entirely rewritten to use the method I described above.

Here is the code I added:
Public currentSheet As Worksheet
Public newSheet As Worksheet
Public myLastCell As Range

Sub MIDataCleanup()

    Set currentSheet = ThisWorkbook.ActiveSheet
    Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count))
    Set myLastCell = LastCell(currentSheet)
    
    
    copyColumn FromColumn:="N", ToColumn:="A"           ' GSD Ticket Number
    copyColumn FromColumn:="A", ToColumn:="B"           ' IR No
    copyColumn FromColumn:="M", ToColumn:="C"           ' Incident Manager
    
    copyColumn FromColumn:="O", ToColumn:="D"           ' Technical Call
    newSheet.Range("D:D").Replace What:="Yes", Replacement:="True", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    newSheet.Range("D:D").Replace What:="NO", Replacement:="False", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    newSheet.Range("E1").FormulaR1C1 = "Reconvene"      ' New Column called Reconvene
    
    copyColumn FromColumn:="P", ToColumn:="F"           ' Incident Priority
    copyColumn FromColumn:="Q", ToColumn:="G"           ' Region
    copyColumn FromColumn:="L", ToColumn:="H"           ' Services Impacted
    
    copyColumn FromColumn:="B", ToColumn:="I"           ' Start Date of Incident
    SeparateDateAndTime Column:="I"
    newSheet.Range("I1").FormulaR1C1 = "Start Date of Incident"
    newSheet.Range("J1").FormulaR1C1 = "Outage Start Time"
    
    copyColumn FromColumn:="C", ToColumn:="K"           ' IMG Engaged Date/Time
    SeparateDateAndTime Column:="K"
    newSheet.Range("K1").FormulaR1C1 = "IMT Engaged Date"
    newSheet.Range("L1").FormulaR1C1 = "IMT Engaged Time"
    
    copyColumn FromColumn:="D", ToColumn:="M"           ' IMG Managed Date/Time
    SeparateDateAndTime Column:="M"
    newSheet.Range("M1").FormulaR1C1 = "IMT Managed Start Date"
    newSheet.Range("N1").FormulaR1C1 = "IMT Managed Start Time"
    
    copyColumn FromColumn:="E", ToColumn:="O"           ' First Support Team Paged Date
    SeparateDateAndTime Column:="O"
    newSheet.Range("O1").FormulaR1C1 = "First Support Team Paged Date"
    newSheet.Range("P1").FormulaR1C1 = "First Support Team Paged Time"

    copyColumn FromColumn:="G", ToColumn:="Q"           ' Initial IR Sent Date
    SeparateDateAndTime Column:="Q"
    newSheet.Range("Q1").FormulaR1C1 = "First Support Team Paged Date"
    newSheet.Range("R1").FormulaR1C1 = "First Support Team Paged Time"

    copyColumn FromColumn:="F", ToColumn:="S"           ' Problem Solver Date
    SeparateDateAndTime Column:="S"
    newSheet.Range("S1").FormulaR1C1 = "Problem Solver Notified Date"
    newSheet.Range("T1").FormulaR1C1 = "Problem Solver Notified Time"
    
    copyColumn FromColumn:="I", ToColumn:="U"           ' Health Check Date
    SeparateDateAndTime Column:="U"
    newSheet.Range("U1").FormulaR1C1 = "Successful Health Check Date"
    newSheet.Range("V1").FormulaR1C1 = "Successful Health Check Time"
    
    copyColumn FromColumn:="H", ToColumn:="W"           ' Service Date
    SeparateDateAndTime Column:="W"
    newSheet.Range("W1").FormulaR1C1 = "Service Available Date"
    newSheet.Range("X1").FormulaR1C1 = "Service Available Time"
    
    copyColumn FromColumn:="K", ToColumn:="Y"           ' IMT Disengaged Date
    SeparateDateAndTime Column:="Y"
    newSheet.Range("Y1").FormulaR1C1 = "IMT Engagement End Date"
    newSheet.Range("Z1").FormulaR1C1 = "IMT Engagement End Time"
    
    copyColumn FromColumn:="J", ToColumn:="AA"           ' IMT Admin Tasks Date
    SeparateDateAndTime Column:="AA"
    newSheet.Range("AA1").FormulaR1C1 = "IMT Admin Tasks Completed Date"
    newSheet.Range("AB1").FormulaR1C1 = "IMT Admin Tasks Completed Time"
    
    copyColumn FromColumn:="R", ToColumn:="AC"           ' IMT Admin Tasks Date
    
    Dim newLastCell As Range
    Set newLastCell = LastCell(newSheet)
    
    With newSheet.sort
        .SetRange Range(Cells(1, 1), Cells(newLastCell.Row, newLastCell.Column))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Dim oldSheetName As String
    
    oldSheetName = currentSheet.Name
    Application.DisplayAlerts = False
    currentSheet.Delete
    Application.DisplayAlerts = True
    newSheet.Name = oldSheetName
    
End Sub

Sub SeparateDateAndTime(Column As String)

    For Each Cell In newSheet.Range(Column & "2:" & Column & myLastCell.Row)
        myStr = Cell.Value
        Cell.Value = Mid(myStr, 4, 3) & Left(myStr, 3) & Mid(myStr, 7, 4)
        Cells(Cell.Row, Cell.Column + 1) = Mid(myStr, 12, 5)
        Cell.NumberFormat = "mm/dd/yyyy"
        Cells(Cell.Row, Cell.Column + 1).NumberFormat = "hh:mm"
    Next

End Sub

Private Sub copyColumn(FromColumn As String, ToColumn As String)
    newSheet.Range(ToColumn & ":" & ToColumn).Value = currentSheet.Columns(FromColumn & ":" & FromColumn).Value
End Sub

Open in new window

MIDataCleanup.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobSampsonCommented:
@Ken, that's a good idea, especially for maintenance, as you mentioned.  @RRave26, can you please try Ken's code?  I'm short of time right now, so won't be able to look at this today.

Rob.
0
Rrave26Author Commented:
Hello Ken,

Thanks for your help.  I have tried your code but there is compile error on line 9 of your code.  I am being told the the lest cell reference is getting a sub or function not defined.
0
Ken ButtersCommented:
The missing function was in the spreadsheet I attached... However, I didn't include it in the comments.

Here is the function you need to have.

Function LastCell(ws As Worksheet) As Range

'
' Note "&" denotes a long value; "%" denotes an integer value
  
    Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

    On Error Resume Next

    With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

    End With

' Finally, initialize a Range object variable for
' the last populated row.
    
    Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

Open in new window

0
Rrave26Author Commented:
thanks Ken, I will try this out tonight.  Can you tell me if this goes into the same module, or should I put this in a different module?
0
Ken ButtersCommented:
Same module.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.