Thread: copying text to empty cells

Hey there,

I currently have created a form for the user to fill in details on my sheet. My idea is that when the user clicks a button, the macro will take that piece of text and populate my other sheet into the correct column.

Therefore the task I am trying to resolve is copy the text into a field and then if the user clicks copy again, the text will fill into the field below as it is empty and so forth until the user has finished entering values.

I have the following setps in pseudocode below but having trouble converting it to a macro!

    copy text
    if target cell is empty
       paste contents into that cell
    else 
       paste contents into row below
    end if statement

i assume this will be wrapped around in a loop so that it can keep incrementing a count to count down the rows but not sure where to put this either.

thanks in advance for your help. Im really struggling with this as I am very unfamiliar with macro language.

I've also attached the spreadsheet im working on.

Cheers

Jag

Hello Jagdeep,

The macro below has been added to the attached workbook and assigned to the "Enter NC" button. The macro is in "Module4".

Sub CopyNCInfo()

  Dim ColArray As Variant
  Dim LastRow As Long
  Dim NextRow As Long
  Dim Rng As Range
  Dim NCWks As Worksheet
  Dim SepWks As Worksheet
  
    Set NCWks = Worksheets("Add_Non_Conformity_Sheet")
    Set SepWks = Worksheets("Seperated_Values")
    
      With SepWks
        LastRow = .UsedRange.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
        NextRow = IIf(LastRow < 2, 2, LastRow + 1)
      End With
      
      ColArray = Array(2, 4, 5, 7, 9, 6, 8, 27, 26, 10, 22, 23, 25, 24, 11, 28)
      
      For I = 0 To UBound(ColArray)
        SepWks.Cells(NextRow, ColArray(I)) = NCWks.Cells(I + 3, "B")
      Next I
      
End Sub