Copy Data To Worksheet 1 - Need Data Worksheet 2 To Become Blank

[RoyJr]

Hello,

Using the below formula to copy the date from worksheet2 to worksheet1. It is working as it should (no date appears on worksheet1 until a date is entered on worksheet2. Once this occurs, the order on worksheet1 is closed and the order on worksheet2 disappears.)

=IF(Completed!$M$4="","",Completed!$M$4)

The problem I need help with is "once I have entered the date on worksheet2 and the formula executes as required, the date remains on worksheet2. I need the worksheet2 cell (sometimes multiple cells) to become blank again until another date is entered."

Thank you,

Roy

[#2]

@RoyJr Perhaps a simple macro would be more efficient instead of formulas to avoid circular references. Pls see a sample in the attached workbook. Go to VBA Editor to check the macro codes and make the change to the cell reference and Sheet names as needed.

The code is based on certain assumptions:

- Entered date is always inputted manually in cell A5 of Sheet 1. Cell A5 is formatted as Short Date
- Date in cell A5 of Sheet 1 would be transferred to the next row below the last used row in column A of Sheet 2 (alternative code available if Date in cell A5 of Sheet 1 would be always transferred to a specific cell A2 of Sheet 2 for example)
- Cell A5 of Sheet 1 would be cleared once its date value has been transferred to Sheet 2 (alternative code available if multiple cells of Sheet 1 need to be cleared once date is transferred to Sheet 2)
- Column A of Sheet 2 is formatted as Short Date

- The workbook must be saved either as .xlsm or .xlsb to contain the VBA macro

Disclaimer: I did not touch VBA for years so the codes would not be neat but it is functional I guess.

[#3]

@hynguyen 

Still working on this. Once complete and good-to-go will let you know.

Thanks.

[#4]

@hynguyen 

Thanks for the workbook example. If possible, can you paste the code in the message? I am working from two computers and one does not have a full excel suite, so I cannot open/copy and paste.

Regards,

Roy

[#5]

@RoyJr Here are the codes:

Sub Fill_Contents_Across_Sheets()
' Assumption: Entered date is always inputed manually in cell A5 of Sheet 1. Cell A5 is formatted as Short Date
' Assumption: Date in cell A5 of Sheet 1 would be transferred to the next row below the last used row in column A of Sheet 2
' Assumption: Cell A5 of Sheet 1 would be cleared once its date value has been transferred to Sheet 2
' Assumption: Column A of Sheet 2 must be formatted as Short Date

Dim LastRow As Long

With ThisWorkbook
' Copy the date entered in cell A5 of Sheet 1
.Sheets("Sheet1").Range("A5").Copy
'Find the last used row in column A of Sheet 2
LastRow = .Sheets("Sheet2").Cells(.Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
'Paste special value and format the date from cell A5 of Sheet 1 to next row below the last used row in column A of Sheet 2
'.Sheets("Sheet2").Range("A" & LastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

' If the date is only transferred to one permanent cell, e.g. A2 of Sheet 2 then add ' before previous black-font line
' and remove ' before next green line
.Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
' Clear content in cell A5 of Sheet 1
.Sheets("Sheet1").Range("A5").ClearContents
' If want to clear content in multiple cells in Sheet 1, e.g. A5, A10, D15, E20, add ' before previous black-font line
' and remove ' before next green line
'.Sheets("Sheet1").Range("A5", "A10", "D15", "E20").ClearContents

' Leave the cursor to cell A5 of Sheet 1
.Sheets("Sheet1").Range("A5").Activate
End With
End Sub