none
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
            Beep
            Exit Sub
        End If
        Set wbk = ActiveWorkbook
        Range("A9:S116").Copy
        Workbooks("MediaSpreadsheet_1.0.xlsm").Activate
        Sheets("OrchestrateData").Select
        Range("A1").Select
        ActiveSheet.Paste
        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
        wbk.Close
        Sheets("MediaSchedule").Select
    End Sub


    DAPulliam64


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

Answers

  • 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:

    https://msdn.microsoft.com/en-us/library/office/ff837425.aspx


    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
    Moderator