Results 1 to 3 of 3

Date Not Formatting Correctly

Threaded View

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    SC
    MS-Off Ver
    Excel 2010
    Posts
    26

    Date Not Formatting Correctly

    I am using the code below to breakout some data into sheets. However, when the data is copied over into the sheets the dates are not formatted correctly. Does anyone have a quick fix to add to the macro for this problem?

    Dim LastRow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
    Dim ws As Worksheet, r As Range, iCol As Integer, t As Date, Prefix As String
    Dim sh As Worksheet, Master As String, Folder As String, Fname As String
    On Error Resume Next
    Set r = Application.InputBox("Click in the column to extract by", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub
    iCol = r.Column
    t = Now
    Application.ScreenUpdating = False
    With ActiveSheet
        Master = .Name
        LastRow = .Cells(Rows.Count, "R").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        iStart = 2
        For i = 2 To LastRow
            If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
                iEnd = i
                Sheets.Add after:=Sheets(Sheets.Count)
                Set ws = ActiveSheet
                On Error Resume Next
                ws.Name = .Cells(iStart, iCol).Value
                On Error GoTo 0
                ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
                .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy
                ws.Range("A2").PasteSpecial Paste:=xlPasteValues
                iStart = iEnd + 1
            End If
        Next i
    End With
    Last edited by sam8114; 06-25-2013 at 02:03 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1