Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: when a cell is empty insert the value of another cell in a table

    Hello,
    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.



    Is there a possibility to do this?

    Thank you for your help.
    Last edited by Mc Blue; 04-28-14 at 09:04.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    this will go thru each row in Source sheet ,check for null date, the pastes into target sheet

    Code:
    Public Sub CopyRecs()
    Dim r As Long, t As Long
    
    Sheets("sheet1").Select   'source data sheet
    Range("a1").Select
    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
            Selection.Copy
            
            Sheets("sheet2").Select   'goto target sheet
            Range("a1").Select
            Selection.End(xlDown).Select     'goto bottom
            ActiveCell.Offset(1, 0).Select  ''goto next empty row
            t = ActiveCell.Row
            
            Rows(t & ":" & t).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
        
            Sheets("sheet1").Select   'back to source sheet
       End If
       ActiveCell.Offset(1, 0).Select  'next row
    Wend
    End Sub

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    Thbank you mvery much Ranman, it worked perfectly!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •