Copy cells if they contains value from target range

Brass Contributor




Looking for micro that wold Copy cells if they contains value from target range and paste them in different location 



Copy cells if they contain value in rang D11-D25 and past the cells with the value in T10 Down 



Thank s 




1 Reply


Here is an example of a VBA macro:


Sub CopyCells()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim cell As Range
    Dim targetRow As Long
    Set sourceRange = Workbooks("SourceWorkbook.xlsx").Worksheets("Sheet1").Range("D11:D25")
    Set targetRange = Worksheets("Sheet2").Range("T10")
    targetRow = targetRange.Row
    For Each cell In sourceRange
        If Not IsEmpty(cell) Then
            Worksheets("Sheet2").Cells(targetRow, targetRange.Column).PasteSpecial xlPasteValues
            targetRow = targetRow + 1
        End If
    Next cell
End Sub


In this example, the source range is in a workbook named SourceWorkbook.xlsx and a worksheet named Sheet1.

You can specify the name of the workbook and worksheet by replacing SourceWorkbook.xlsx and Sheet1 with the names of your source workbook and worksheet in the sourceRange line of the code.

It is important to note that for this macro to work, both the source workbook and the workbook containing the macro must be open.

If the source workbook is not open, you will need to add code to open it before running the macro.

After making these changes, you can run the macro to copy cells from the source range in the specified workbook and worksheet and paste them in the specified location in the target worksheet.

To use this macro, you can press Alt + F11 to open the Visual Basic editor in Excel.

Then, right-click on the name of your workbook in the Project Explorer and select Insert > Module. Paste the code into the new module and close the Visual Basic editor.

You can then run the macro by pressing Alt + F8, selecting the CopyCells macro from the list, and clicking Run.


I hope this helps!