Hi,
I have copied and pasted a code (courtesy of contexture) and adapted it to work with my worksheet. This macro allows to add info from one sheet to another if all cells are filled in, however, I don't wish to have such restriction and that it can still add info to another sheet if some cells are empty (it currently has a pop up message alerting me to 'Please fill in all the cells'). How do I go about doing this in the current code?
Your help is much appreciated.
Sub UpdateLogWorksheet()
Dim databaseWks As Worksheet
Dim formWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
'cells to copy from Form sheet - some contain formulas
myCopy = "D3,D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25"
Set formWks = Worksheets("Selection Profile")
Set databaseWks = Worksheets("Database")
With databaseWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With formWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With databaseWks
With .Cells(nextRow, "A")
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 1
For Each myCell In myRng.Cells
databaseWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear form cells that contain constants
With formWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub
Bookmarks