PDA

View Full Version : Code for not add the data in Sheet2 if all cells in range in Sheet1 are not filled



ginodauri
07-09-2013, 01:18 PM
Hello all

Please if someone can help me with the problem.
In Sheet1 I have the data from range D6 : D13.
After the user is filling the required data with command button it copies it to Sheet2 where I have
formatted the table as Table.

The answer which I'm looking for is that someone please provide me the Vba code that it's not copying the data if the range from D6 : D13 is not filled completely.

On Sheet1 on Command button I've added the code which works and copy the data in Sheet2 but I have to restrict it not to copy if the criteria for all data in range D6 : D13 is not met.

And yes, at the end is when data is added that range is reseted but the current date stays. (in D8)

Any help is highly appriciated.

NOTE: Through the User form I can do this but now I need it for data which are entered in sheet and not the user form.

Regards!

Here is the code for Command button:


Private Sub CommandButton1_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Set the_sheet = Sheets("Sheet2")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Sheet1").Range("D6")
table_object_row.Range(1, 2).Value = Worksheets("Sheet1").Range("D7")
table_object_row.Range(1, 3).Value = Worksheets("Sheet1").Range("D8")
table_object_row.Range(1, 4).Value = Worksheets("Sheet1").Range("D9")
table_object_row.Range(1, 5).Value = Worksheets("Sheet1").Range("D10")
table_object_row.Range(1, 6).Value = Worksheets("Sheet1").Range("D11")
table_object_row.Range(1, 7).Value = Worksheets("Sheet1").Range("D12")
table_object_row.Range(1, 8).Value = Worksheets("Sheet1").Range("D13")
'clear range but leave the current date in D8 in Sheet1
Range("D6:D7,D9:D13").Select
Range("D6").Activate
Selection.ClearContents
Range("D6").Select
End Sub

ginodauri
07-10-2013, 12:23 AM
Just to inform that I've managed (with help from other person) to solve this problem in the meanwhile.