Unanswered: when a cell is empty insert the value of another cell in a table
I m struggling to find a way for this:
in an excel database,
I have a sheet (sheet 1) with a column with order number (B) and a column with end dates (S).
I would like to have in a table (sheet 2) all the items in (B) when (S) has no data.
In a way that when I refresh my database and pick the selected country the table is updated automatically.
With the orders that don't have any end date.
and cherry on the cake, it has also to match the country that is preselected in a dropdown menu on top of the table. Country that appear in the sheet one in column H.
this will go thru each row in Source sheet ,check for null date, the pastes into target sheet
Public Sub CopyRecs()
Dim r As Long, t As Long
Sheets("sheet1").Select 'source data sheet
While ActiveCell.Text <> "" 'scan a column w values in every cell
If ActiveCell.Offset(0, 18).Value = "" Then 'check col S for empty
r = ActiveCell.Row
Rows(r & ":" & r).Select
Sheets("sheet2").Select 'goto target sheet
Selection.End(xlDown).Select 'goto bottom
ActiveCell.Offset(1, 0).Select ''goto next empty row
t = ActiveCell.Row
Rows(t & ":" & t).Select
Application.CutCopyMode = False
Sheets("sheet1").Select 'back to source sheet
ActiveCell.Offset(1, 0).Select 'next row