VBA Macro not working

Microsoft Excel 2003 (full product)
December 14, 2009 at 10:02:16
Specs: Windows XP
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
End If
End If
End Sub
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.

See More: VBA Macro not working

Report •

December 14, 2009 at 11:58:53
Note the title of the macro:

Private Sub Worksheet_Change

Worksheet_Change macros monitor the sheet whose module they are stored in and fire whenever a change is made to that sheet.

Right Click the sheet tab for Sheet1 and chose View Code

Paste the code into the module that opens, which will be the Sheet1 module. It should now run whenever a change is made to Sheet1.

I suggest that you make a backup copy of your workbook before you do this just in case something goes terribly wrong.

Report •

December 17, 2009 at 06:47:57
Thank you so much for your help DerbyDad03,
You are a gentleman and a scholar.
It worked like a charm and you are a good teacher.
Drawing my attention to The title of the macro was actually very helpful. I had not realized the name was more than just a name but an instruction. I also did not know that I could place a macros inside a specific sheet.
I changed the reference to $0$3 in the formulas in columns 15 & 16 to just contain TODAY() so I avoid the link to sheet1 problem.

Thanks again for all your help. It is awesome.

Report •

December 17, 2009 at 07:30:23
Glad I could help...

Report •

Related Solutions

Ask Question