Using a Macro to add a Carriage Return after each data entry within all cells of an imported range of cells RRS feed

  • Question

  • I have a macro that copies data from a Target workbook then pastes the data into a destination workbook.  I then wish to use lookups in the destination workbook to view specific data from the pasted range of data on a separate sheet.  The problem is, the cells that contain numbers from the pasted data have the green dogeared error flags associated with the cell. The only way I can make a lookup function work, is to go to each cell and manually enter a carriage return after each entry.  The code for the macro is given below.  What can be done so that the pasted data contains no errors associated with the number cells?  Or can a second macro be written to clean the data.  If so can you help me out?  Thanks in advance.

    Kindest Regards

    Sub ImportData()
     ' ImportData Macro allows user to select an Excel workbook (i.e. Orchestrate Excel Output),
     ' then copy & paste it into the MediaSpreadsheet.
        Dim wbk As Workbook
        Set wbk = Application.Run("MediaSpreadsheet_1.0.xlsm!OpenFile")
        If wbk Is Nothing Then
            Exit Sub
        End If
        Set wbk = ActiveWorkbook
        With Selection.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    End Sub


    • Edited by DAPAugust64 Monday, February 9, 2015 9:20 PM
    Monday, February 9, 2015 9:17 PM


  • Hi DAPAugust64,

    The green "dogeared error flags" of the cells means that you paste numbers in these cells, but they're formated as Text.

    So you need to change the cells back to the correct format so that the lookup function works fine. You can simply call this method after pasting:

    ActiveSheet.Cells(1, 1).NumberFormat = "General"
    ActiveSheet.Cells(1, 1) = ActiveSheet.Cells(1, 1).Text

    Or use PasteSpecial method to paste the numbers as well as it's format:

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 11, 2015 2:27 AM