Forum Discussion

Veraborn64's avatar
Veraborn64
Copper Contributor
Aug 10, 2020

Filling in blank cells with values from another workbook

Hello all, I'm looking for help writing a macro which does the following but struggling with a few areas, I'd like to do the following:    1) Identify all blank cells in a given range. All blank ce...
excel
Macros and VBA
JKPieterse's avatar
JKPieterse
Silver Contributor
Aug 10, 2020

Veraborn64 You could use a macro similar to this:

Sub FillTheGaps()
    Dim sourceCell As Range
    Dim NIR As Workbook
    Dim VIS As Workbook
    Set NIR = ThisWorkbook 'Replace this with something like Workbooks("NIR.xlsx")
    Set VIS = ThisWorkbook 'Replace this with something like Workbooks("VIS.xlsx")
    'Assuming the name of the worksheet is the same
    
    For Each sourceCell In NIR.Worksheets("Sheet1").Range("C8:M100").SpecialCells(xlCellTypeBlanks)
        sourceCell.Value = VIS.Worksheets("Sheet1").Range(sourceCell.Address).Value
    Next

End Sub

 

Resources