Hi there,
First Post!
I'm trying to move a row from sheet "Future Appointments" to "Today's Appointments" IF the date in column A is today's date. I've been trying lots of different ways all day, but my macro is either 1. Not moving the row or 2. Moving the row regardless of date.
The two sheets are identical in terms of formatting and layout, just different names.
I'm running the macro from "Future Appointments" sheet.
I've written =TODAY() in E1 to use today's date as a reference (on both sheets).
I am inexperienced in VBA, and have been recording macros then editing the code. At the moment I have this:
Range("A9").Select
If A9 = E1 Then
Rows("9:9").Select
Selection.Copy
Sheets("Today's Appointments").Select
Rows("4:4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Future Appointments").Select
Selection.Delete Shift:=xlUp
Else
MsgBox "No values found"
End If
End Sub
It's the first couple of lines that appear to be the problem.
I've tried comparing A9 to E1 in the above code as follows...
Range("A9").Select
If A9 = E1 Then
Rows("9:9").Select
This is moving the row regardless of whether A9 is today's date or a future date.
I've tried using IF function at cell AA9 (=IF(A9=E1, "YES", "NO") and then the following...
Range("AA9").Select
If AA9 = YES Then
Rows("9:9").Select
This is moving the row regardless of whether AA9 is YES or NO. I tried with "YES" instead of YES, this never moves the row and always returns No Values Found (i.e. the Else msgbox).
I've tried pasting the values from AA9 to AB9 in case it needed to refer to a cell that purely had the value YES without a formula present...
Range("AB9").Select
If AB9 = "YES" Then
Rows("9:9").Select
This is the same as the previous example, "YES" moves nothing and YES moves the row regardless.
The rest of the macro appears to be functioning fine, i.e. it changes to the other sheet, copies the row, then goes back to delete the row.
I tried various other iterations of the above examples to no avail. I would appreciate any help at all anyone can give me!
Thanks in advance
Fraser
First Post!
I'm trying to move a row from sheet "Future Appointments" to "Today's Appointments" IF the date in column A is today's date. I've been trying lots of different ways all day, but my macro is either 1. Not moving the row or 2. Moving the row regardless of date.
The two sheets are identical in terms of formatting and layout, just different names.
I'm running the macro from "Future Appointments" sheet.
I've written =TODAY() in E1 to use today's date as a reference (on both sheets).
I am inexperienced in VBA, and have been recording macros then editing the code. At the moment I have this:
Range("A9").Select
If A9 = E1 Then
Rows("9:9").Select
Selection.Copy
Sheets("Today's Appointments").Select
Rows("4:4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Future Appointments").Select
Selection.Delete Shift:=xlUp
Else
MsgBox "No values found"
End If
End Sub
It's the first couple of lines that appear to be the problem.
I've tried comparing A9 to E1 in the above code as follows...
Range("A9").Select
If A9 = E1 Then
Rows("9:9").Select
This is moving the row regardless of whether A9 is today's date or a future date.
I've tried using IF function at cell AA9 (=IF(A9=E1, "YES", "NO") and then the following...
Range("AA9").Select
If AA9 = YES Then
Rows("9:9").Select
This is moving the row regardless of whether AA9 is YES or NO. I tried with "YES" instead of YES, this never moves the row and always returns No Values Found (i.e. the Else msgbox).
I've tried pasting the values from AA9 to AB9 in case it needed to refer to a cell that purely had the value YES without a formula present...
Range("AB9").Select
If AB9 = "YES" Then
Rows("9:9").Select
This is the same as the previous example, "YES" moves nothing and YES moves the row regardless.
The rest of the macro appears to be functioning fine, i.e. it changes to the other sheet, copies the row, then goes back to delete the row.
I tried various other iterations of the above examples to no avail. I would appreciate any help at all anyone can give me!
Thanks in advance
Fraser