Copy and Paste Excel Macro for only rows with data

Question

I need help in figuring out a macro to copy and paste only values from rows that contain data (and not a large set range) to another worksheet.

The worksheet named DataEntry is where downtime is entered in columns A - J (could have 2 rows of data or could have 50) and pastes only the values (and not formulas) into another worksheet named DataCombined to column A and on the next available row.

Please help!

takanuva15 takanuva15 · Accepted Answer · 2017-07-13T13:21:39

Your question is kind of confusing, but this may be where you want to start: (If you could please give an example sheet with your desired output, that would help)

Sub copyRange()
    Dim dataSht As Worksheet, comboSht As Worksheet
    Set dataSht = Sheets("DataEntry")
    Set comboSht = Sheets("DataCombined")

    Dim rngToCopy As Range

    Set rngToCopy = dataSht.Range(Cells(2, 1), Cells(getLastFilledRow(dataSht), "J"))
    rngToCopy.Copy
    comboSht.Range("A" & (getLastFilledRow(comboSht) + 1)).PasteSpecial xlPasteValues
    rngToCopy = dataSht.Range(Cells(2, 1), Cells(getLastFilledRow(dataSht), "J")).SpecialCells(xlCellTypeConstants)
    rngToCopy.ClearContents
End Sub

'Gets the last row of the worksheet that contains data
Public Function getLastFilledRow(sh As Worksheet) As Integer
    On Error Resume Next
    getLastFilledRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Copy and Paste Excel Macro for only rows with data

1 Answers