|I would like a macro to help me speed up my work and save me some time.|
But I do not know enough about VBA to make the suggested solution work.
Background of Workbook:
We are tracking Open and Closed files.
I have three worksheets in my workbook, Sheet1 is called “OPEN”, Sheet2 is called “CLOSED”.
Sheets 1 & 2 have identical columns and column headings. But Sheet1 (OPEN) will be the main sheet where all the data is inputted with the exception of one column.
(Column M or number 13 with the heading “Date Closed YYYY-MM-DD”)
I have formulas in columns N, O, P that track days open based on Column A and Column M and $O$3 is =TODAY() on both sheets. I don’t want a link to the OPEN page to be placed in the formula when it is moved.
When this column (M) does get data entered, I want the spreadsheet to move all the data for the entire row From Sheet1 (OPEN) to the next available (empty) row on Sheet2 (CLOSED).
I then want the row and data that used to be on Sheet1 (OPEN) to be deleted so that I don’t have duplicate or empty rows in the middle of Sheet1 (OPEN)
Then, the next time this action occurs, I will need it to move the data to the next row down on Sheet2 (CLOSED).
I would like this work to happen in the background and automatically for both existing entries and for new ones as soon as they occur.
I did some looking around and found a reference that may be helpful.
Excel: remove data to another sheet. Solution provided by DerbyDad03 on April 26, 2008
If the code below is good, then I will need instructions for how to get this to work on my spreadsheet.
The suggested Visual Basic (VBA) code looks something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the Target is in
'Column 13 and contains data
If Target.Column = 13 And Target.Cells.Count = 1 Then
If Target.Value <> "" Then
'Store Target Address
MyAddress = Target.Address
'Find Next Empty Cell In Sheet2 Column A
NextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Cut and Paste Target Row to Sheet2
Target.EntireRow.Cut Destination:=Sheets(2).Range("A" & NextRow)
'Delete Target Row in Sheet1
Problem is I don’t know anything about VBA.
I open VBA, create a module, paste the code below but then I can’t get anything to happen and it does not show up on the Run menu.
Thank you and I look forward to your reply.