Copy a specific cell value to column if adjacent column contains data

[bunny1122]

Hi All

I have a macro that will extract data from other workbooks and append it to another excel everyday based on the date I input in cell B1.

A sample data below: The data range that is appended daily is column B:K. Then, I have to manually copy the cell(report date) in B1 and paste to Column A accordingly. I need a macro to automate this part, appreciate any inputs. Thank you!

Note: The rows of the data range is not fixed.

 

[bebo021999]

Some thing like this:

With Range("A3").CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks)
    .Value = Range("B1")
    .NumberFormat = "dd/mm/yy"
End With

 

[bunny1122]

Some thing like this:

With Range("A3").CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks)
    .Value = Range("B1")
    .NumberFormat = "dd/mm/yy"
End With

Thank you! This works on my sample data. I've come across an issue however.

On my actual file, my header ("report date") starts from row 6, when I run the code, the date will populate correctly onto the excel under "Report Date", but the date will also appear row row 4 and 5. I tried this on my sample data without issue though. How can I fix that?

Screenshot of results after running the code

Below is the code I'm using to run.

'Update Reporting Date

    With Range("A6").CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks)
    .Value = ThisWorkbook.Sheets("Source").Range("B1")
    .NumberFormat = "dd-Mmm"
End With

 

[bebo021999]

Maybe:

Dim cell As Range
For Each cell In Range("A6").CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks)
    With cell
        If .Row > 6 Then
            .Value = Range("B1")
            .NumberFormat = "dd-Mmm"
        End If
    End With
Next

 

[bebo021999]

Or, with no loop, code speed may better:

With Range("A7:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    .Value = Range("B1")
    .NumberFormat = "dd-Mmm"
End With

 

[bunny1122]

Or, with no loop, code speed may better:

With Range("A7:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    .Value = Range("B1")
    .NumberFormat = "dd-Mmm"
End With

Thank you so much! Works like a charm