Help with Autoformat pls

[fwawergurl16]

Hi. Im an intern in Malaysia. I've cleaned up some spreadsheets & consolidated them. The problem is that when I consolidate, my data is changed from text to date format.How can I make it stay as text when I consolidate?

Eg: Original text: 5/13, 10/53, 8/37 (it's column size per cm)
After consolidation: May-13, October-53, August-37

Does any1 know how to get about this? All help much appreciated! Thanks!!

fwawergurl16

[anandbohra]

set the format of the cell as text then type or paste your data it will remain 5/13 instead of May-13

[fwawergurl16]

set the format of the cell as text then type or paste your data it will remain 5/13 instead of May-13

I did that already, but it doesn't work. I'm not sure if my codes are correct also..below's the code. If it's incorrect, would u know how to correct it? Thanks loads!

Sub formatMasterCol()
 Columns("I:I").Select
 Selection.NumberFormat = "@" 'Text
End Sub

[geekgirlau]

When you say you are "consolidating" them, what exactly are you doing?

[fwawergurl16]

When you say you are "consolidating" them, what exactly are you doing?

I posted for help on the consolidation work last time in this forum (Solved: Copy Paste using macro involving 6 different workbooks). Well, what happens when macro runs:
1. prompts user to select folder to clean
2. cleans all excel sheets in folder (several Excel sheets inside. Each .xls has one sheet only)
3. create a new folder called 'Cleaned' & dumps every cleaned file into it with a saved as name (new name)
4. creates a new workbook called Master
5. copies from all the cleaned files & pastes into the Master. Saves Master. Macro ends.

So now, I'm facing some probs with the formatting when I paste it into the Master. In the cleaned file, it is ok. But once it transfers, the formatting goes bonkers.

Eg: Original text: 5/13, 10/53, 8/37 (it's column size per cm)
After consolidation: May-13, October-53, August-37'

How to solve this when I've already included the codes (in previous reply) into my macro? All help much appreciated!!

[rory]

Try changing the consolidation macro to this version, which will actually copy the cells (including formatting) rather than just using the values:

Sub Consolidation()
Dim wbk As Workbook, wbkMaster As Workbook
Dim wksSource As Worksheet, wksDest As Worksheet
Dim StrFile As String, strPath As String
Dim rngLastCell As Range
Dim lngRowCount As Long, lngTargRow As Long, lngCounter As Long
Dim varData
Dim strSlash As String
strSlash = Application.PathSeparator
' Note: to use fixed path, uncomment next line and change to whatever path you want
'    strPath = "C:\Test"
' Allows you to pick a folder each time
strPath = GetFolder
If strPath = "" Then
    MsgBox "You must choose a path!"
    Exit Sub
    End If
    Application.ScreenUpdating = False
    lngCounter = 1
    If Right$(strPath, 1) <> strSlash Then strPath = strPath & strSlash
    If Dir(strPath & "cleaned", vbDirectory) = "" Then MkDir strPath & "cleaned"
    StrFile = Dir(strPath & "*.xls")
    Set wbkMaster = Workbooks.Add
    Set wksDest = wbkMaster.Worksheets(1)
    wksDest.Name = "Data1"
    lngTargRow = 2
    Do Until StrFile = ""
    If Not StrFile = ThisWorkbook.Name Then
        Set wbk = Workbooks.Open(strPath & StrFile)
        If CleanFile(StrFile) = True Then
            ' Assumes only one sheet
            Set wksSource = wbk.Worksheets(1)
            Set rngLastCell = LastCellInSheet(wksSource)
            lngRowCount = rngLastCell.Row - 1
            If lngTargRow + lngRowCount - 1 > 65536 Then
                lngCounter = lngCounter + 1
                Set wksDest = wbkMaster.Sheets.Add
                wksDest.Name = "Data " & lngCounter
                lngTargRow = 2
            End If
            With wksSource
                .Range(.Cells(2, "A"), rngLastCell).Copy wksDest.Cells(lngTargRow, 1)
            End With
            lngTargRow = lngTargRow + lngRowCount
        End If
        wbk.SaveAs strPath & "cleaned" & strSlash & Replace$(wbk.Name, ".xls", "") _
        & " clean " & Format$(Now, "yyyy-mm-dd hh.mm") & ".xls"
        wbk.Close False
    End If
    StrFile = Dir
    Loop
    wbkMaster.SaveAs strPath & "Master.xls"
     '   wbkmaster.close
    Application.ScreenUpdating = True
End Sub

[Charlize]

Sub reformat_text()
'To get rid of that annoying green arrow
'The so called helpfull hint system of excel
'This one is for the date
Application.ErrorCheckingOptions.TextDate = False
'First do the formatting as text
With Worksheets(1)
.Columns("I:I").NumberFormat = "@"
'Then the pasting of a value that looks like a date
.Range("I2") = "05/13"
End With
'Set the helpsystem for the date back to true
Application.ErrorCheckingOptions.TextDate = True
End Sub

and add a line to format the destination workbook. Before the Do Until.

wksDest.Columns("I:I").NumberFormat = "@"

[fwawergurl16]

Coolness Rory & Charlize!! Thank u very much for the help!! Appreciate it loads

[Charlize]

Or you could remove the green arrows after all the pasting in the destination workbook

Sub remove_green_arrow_for_text_date()
Dim cell As Range
Application.ErrorCheckingOptions.TextDate = False
For Each cell In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
    cell.Value = cell.Value
Next cell
Application.ErrorCheckingOptions.TextDate = True
End Sub

[fwawergurl16]

Thanks Charlize! Makes the sheet looks neater :P