Reorganizing Worksheet

Chillyfruit · Sep 26, 2011

I'm an excel newbie and i've been doing a lot of googling and it's brought me here a lot and you guys are amazing. So i opted to join this forum because i need some specific help and I'm hoping you guys can help me.

This is a unique request and problem I'm currently facing. I have a worksheet that contains access info into certain rooms with date of access, time of access, access key number and access location. What I'm trying to do is organize the worksheet so that all i'll be looking it is a sheet with all the access key numbers carried out on a specific day in one column (one column per day not multiple columns with the same dates. Here is a sample size of the worksheet.

<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:159.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/12/2011 13:09
</td> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 111111 22222
</td> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-5-CVS Hall OUT
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/12/2011 13:08
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 222222 33333
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-31-Front IN
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/12/2011 13:08
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 333333 44444
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-6-CVS hall IN
</td> </tr> <tr style="mso-yfti-irow:3"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/11/2011 9:38
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 222222 55555
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-31-Front IN
</td> </tr> <tr style="mso-yfti-irow:4"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/11/2011 9:36
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 444444 11111
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-32-Front OUT
</td> </tr> <tr style="mso-yfti-irow:5"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/11/2011 7:40
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 777777 22222
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-15-Pantry IN
</td> </tr> <tr style="mso-yfti-irow:6"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/10/2011 13:01
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 888888 11111
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-16-Pantry OUT
</td> </tr> <tr style="mso-yfti-irow:7"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/10/2011 12:17
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 777777 33333
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-6-CVS hall IN
</td> </tr> <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 9/10/2011 12:11
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 999999 22222
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 15-31-Front IN
</td> </tr> </tbody></table>

Here is what I'm aiming for (I don't care about the location column)

<table class="MsoTableGrid" style="width:6.65in;border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="1" cellpadding="0" cellspacing="0" width="638"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:18.85pt"> <td style="width:159.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:18.85pt" valign="top" width="213"> 9/12/2011
</td> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:18.85pt" valign="top" width="213"> 9/11/2011
</td> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:18.85pt" valign="top" width="213"> 9/10/2011
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 111111 22222
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 222222 55555
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 888888 11111
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 222222 33333
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 444444 11111
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 777777 33333
</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes"> <td style="width:159.6pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 333333 44444
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 777777 22222
</td> <td style="width:159.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="213"> 999999 22222
</td> </tr> </tbody></table>

If this isn't possible what i was eventually aiming at was to compare a column of access numbers against this worksheet to check to see if those access numbers exist/does not exist on the given dates in this worksheet.

Thank you so much.

rdw72777 · Sep 26, 2011

So do you want it to create this new sheet or do you already have the new sheet laid out. Are there column headers for your original data (you didn't show any in the original data, but they are there in your desired result.)?

Chillyfruit · Sep 26, 2011

Yes i would like to create a new sheet for this. There are column headers in the original sheet but those can be ignored. No they aren't in the desired results as i want the dates to be the new column headers.

rdw72777 · Sep 26, 2011

Should be able to get you something tomorrow, have to run today.

Here si the beginnign code though, just need to finish the looping part. test it ouy. it should work, but you might need to modify sheetnames since you didn't provide any.

Code:

Sub Process_Report()

Dim wkb As Workbook
Dim Sheet1 As Worksheet, Sheet2 As Worksheet
Dim RoomCol As Long, AccessKeyCol As Long, TimeCol As Long, Sheet1LastRow As Long
Dim Sheet4PercentageCol As Long, Sheet4LastRow As Long


Set wkb = ThisWorkbook
Set Sheet1 = wkb.Worksheets("Sheet6")

Application.DisplayAlerts = False
Sheets("mysheet").Delete


'=================================================================================================
'=============Define Variables=================================================================
    '======= Sheet1 ==================================
    With Sheet1
    
        '.Select
        TimeCol = 1
        AccessKeyCol = 2
        RoomCol = 3
        Sheet1LastRow = .Cells(Rows.Count, TimeCol).End(xlUp).Row
        
        
        
    End With
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "mysheet"
    Set Sheet2 = wkb.Worksheets("mysheet")
    
    'Trim out day from Date/Time column
    With Sheet1
        .Select
        .Cells(2, RoomCol + 1).FormulaR1C1 = "=ROUND(LEFT(VALUE(RC[-3]),5),0)"
        .Cells(2, RoomCol + 1).Copy
        .Cells(3, RoomCol + 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range(Cells(2, RoomCol + 1), Cells(Sheet1LastRow, RoomCol + 1)).Copy
    
    End With
    
    With Sheet2
        .Select
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Cells(1, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
        Sheet2LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        '.Range(Cells(1, 1), Cells(Sheet2LastRow, 1)).Copy
        '.Cells(1, 2).Paste
        
        .Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("mysheet").Sort
            .SetRange Range(Cells(1, 1), Cells(Sheet2LastRow, 1))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        .Range(Cells(1, 1), Cells(Sheet2LastRow, 1)).Copy
        .Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        .Columns("A:A").Delete Shift:=xlToLeft
        
        sheet2ColumNCount = .Cells(1, Columns.Count).End(xlToLeft).Column
        
    End With

End sub

Chillyfruit · Sep 26, 2011

Hello rdw72777 this is amazing. Thank you so much for putting aside the time to do this. I'm not at work, on my way to the airport and i don't have my laptop with me at the moment but i'll definitely give this a try when i do. Oh ya modifiying the sheet names is the laughable least thing i can do. Really appreciate it

rdw72777 · Sep 27, 2011

Chillyfruit

This should be fully funtional now. Note that the code includes a function called "SheetsExist" to check for existence of a sheet; its code published by others on here and works fine but I can take no credit for it since it was someone else's work.

Enjoy!!!

Code:

Sub Process_Report()

Dim wkb As Workbook
Dim Sheet1 As Worksheet, Sheet2 As Worksheet
Dim RoomCol As Long, AccessKeyCol As Long, TimeCol As Long, Sheet1LastRow As Long
Dim Sheet4PercentageCol As Long, Sheet4LastRow As Long
Dim sheet2ColumnCount As Long, myDay As Long, myDayCheckCol As Long, myAccessKey As Variant
Dim myDayPasteRow As Long

Set wkb = ThisWorkbook
Set Sheet1 = wkb.Worksheets("Sheet6")

'Code to see if report tab created by macro previously is here.  If so, we delete the tab
'since the macro re-creates it EVERY time
If SheetExists("mysheet") Then
    Application.DisplayAlerts = False
    Sheets("mysheet").Delete
End If

'=================================================================================================
'=============Define Variables=================================================================
    '======= Sheet1 ==================================
    With Sheet1
    
        .Select
        TimeCol = 1
        AccessKeyCol = 2
        RoomCol = 3
        Sheet1LastRow = .Cells(Rows.Count, TimeCol).End(xlUp).Row
        
    End With
    
    'Insert new sheet and name it "mysheet", this will be the newly created report
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "mysheet"
    Set Sheet2 = wkb.Worksheets("mysheet")
    
    'Insert formula in column D to trim out day from Date/Time column
    With Sheet1
        .Select
        .Cells(2, RoomCol + 1).FormulaR1C1 = "=ROUND(LEFT(VALUE(RC[-3]),5),0)"
        .Cells(2, RoomCol + 1).Copy
        .Cells(3, RoomCol + 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range(Cells(2, RoomCol + 1), Cells(Sheet1LastRow, RoomCol + 1)).Copy
    
    End With
    
    With Sheet2
        'Paste date values on "mysheet" tab and remove duplicates (these values will serve
        'as headers across the top)
        .Select
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
        Sheet2LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
                
        'Sort the date values
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("mysheet").Sort
            .SetRange Range(Cells(1, 1), Cells(Sheet2LastRow, 1))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        'Paste/transpose newly sorted dates as headers across row 1, beginning in column B
        'Then delete column A since you've opaste/transposed those values across row 1
        .Range(Cells(1, 1), Cells(Sheet2LastRow, 1)).Copy
        .Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        .Columns("A:A").Delete Shift:=xlToLeft
        
        'Counts the number of day headers you have across row 1
        sheet2ColumnCount = .Cells(1, Columns.Count).End(xlToLeft).Column
               
    End With
    
    'Loop through all rows on the initial data tab and put the access key under the correct day's
    'header
    For i = 2 To Sheet1LastRow
    
        myDay = Sheet1.Cells(i, 4).Value
        myAccessKey = Sheet1.Cells(i, AccessKeyCol).Value
        
        myDayCheckCol = Sheet2.Rows(1).Find(What:=myDay, SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
        myDayPasteRow = Sheet2.Cells(Rows.Count, myDayCheckCol).End(xlUp).Row + 1
        Sheet2.Cells(myDayPasteRow, myDayCheckCol) = myAccessKey
        
    Next i
    
    'Format dates on mysheet tab (Sheet2) as dates and bold
    Sheet2.Range(Cells(1, 1), Cells(1, sheet2ColumnCount)).NumberFormat = "m/d/yyyy"
    Sheet2.Range(Cells(1, 1), Cells(1, sheet2ColumnCount)).Font.Bold = True
    
    'Clear formula from initial data tab (formula was used to get date
    Sheet1.Select
    Sheet1.Range(Cells(2, RoomCol + 1), Cells(Sheet1LastRow, RoomCol + 1)).ClearContents
                
End Sub
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

Chillyfruit · Sep 27, 2011

Hey rdw i tried the code out and i was following along what it was doing but ran into a snag. I should have made this clear but the timestamp values are all in one column. So essentially the focus should only be on 2 columns, the timestamp and the acccess numbers.

 <table class="MsoNormalTable" style="width:387.6pt;margin-left:5.4pt;border-collapse:collapse;border:none; mso-border-alt:solid windowtext .5pt;mso-yfti-tbllook:1184;mso-padding-alt: 0in 5.4pt 0in 5.4pt;mso-border-insideh:.75pt solid windowtext;mso-border-insidev: .75pt solid windowtext" border="1" cellpadding="0" cellspacing="0" width="517"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:19.5pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; mso-border-top-alt:.5pt;mso-border-left-alt:.5pt;mso-border-bottom-alt:.75pt; mso-border-right-alt:.75pt;mso-border-color-alt:windowtext;mso-border-style-alt: solid;padding:0in 5.4pt 0in 5.4pt;height:19.5pt" nowrap="nowrap" valign="bottom" width="126"> Time
</td> <td style="width:293.1pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .75pt;mso-border-top-alt: .5pt;mso-border-left-alt:.75pt;mso-border-bottom-alt:.75pt;mso-border-right-alt: .5pt;mso-border-color-alt:windowtext;mso-border-style-alt:solid;padding:0in 5.4pt 0in 5.4pt; height:19.5pt" nowrap="nowrap" valign="bottom" width="391"> Event
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/15/2011 22:02

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 2222
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/15/2011 22:02

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 3333
</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/15/2011 18:00

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 5555
</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/15/2011 15:53

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 4444
</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/14/2011 22:44

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 5555
</td> </tr> <tr style="mso-yfti-irow:6;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/14/2011 22:29

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 4444
</td> </tr> <tr style="mso-yfti-irow:7;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-alt: solid windowtext .75pt;mso-border-left-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/14/2011 13:20

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-alt:solid windowtext .75pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 2222
</td> </tr> <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:94.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .75pt;mso-border-top-alt: .75pt;mso-border-left-alt:.5pt;mso-border-bottom-alt:.5pt;mso-border-right-alt: .75pt;mso-border-color-alt:windowtext;mso-border-style-alt:solid;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="126">

9/13/2011 21:16

</td> <td style="width:293.1pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .75pt;mso-border-left-alt:solid windowtext .75pt; mso-border-top-alt:.75pt;mso-border-left-alt:.75pt;mso-border-bottom-alt: .5pt;mso-border-right-alt:.5pt;mso-border-color-alt:windowtext;mso-border-style-alt: solid;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="391"> 10015352 4444
</td> </tr> </tbody></table>

Also in this line:

Code:

With ActiveWorkbook.Worksheets("mysheet").Sort

Should i replace it with this?

Code:

ActiveWorkbook.Sheet2.Sort?

Since "mysheet" is already being stored in the variable sheet2?

This is a monster of an excel code

.

rdw72777 · Sep 27, 2011

Chilly

Just curious, did the code work. I have a nasty (good?) habit of defining everything I see in my code since I usually end up using it later on. I focused on the first 2 columns,a nd then manually insert a formula in column D to strip out the day from the day/time value in column A.

I tested it on your sample data and it works. if you want to remove the un-necessary stuff feel free, you literally remove any code relating to the third column. If you want to modify the instances of "mysheet" feel free, i think I had trouble using the defined variable "Sheet2" but I forget.

Chillyfruit · Sep 27, 2011

Ok that's why. Here's the the thing, the sheet got bigger with a lot more data in it using up a lot more columns. So columns A through P are being used now, not A through C.

rdw72777 · Sep 27, 2011

Ah, that's important info. if your file is changing then finding the data amongst the columns is important. using either named ranges for whole columns or using the headers to define the data is key. let me know which you want to do and I can update the code to handle such scenarios.

Reorganizing Worksheet

Chillyfruit

New Member

Excel Facts

rdw72777

Well-known Member

Chillyfruit

New Member

rdw72777

Well-known Member

Chillyfruit

New Member

rdw72777

Well-known Member

Chillyfruit

New Member

rdw72777

Well-known Member

Chillyfruit

New Member

rdw72777

Well-known Member

Similar threads

Forum statistics

Share this page

Reorganizing Worksheet

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock