Hi,
I'm trying to create an excel spreadsheet (version 2019 and no ability to upgrade to 365) to keep track of appointments.
There are sheets for each month. And I need the appointments for future dates to show up in their respective monthly tab automatically (without disappearing from the original one).
I have attempted to create a VBA module but the problem I hit is that every time I ran the macro, the information copied over and over. Also, my "If status" only covered one month and I don't know how to cover more months and was trying to avoid creating a macro for each month.
Below is what I came up with from a tutorial I watched but like I said it came short for my needs. Is there a way to replicate the excel 365 recently introduced Filter formula? I think that's what I would need- but again, not sure
Sub CopyOverNextMonthMeetings()
Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Set StatusCol = Sheet1.Range("E2:E20")
For Each Status In StatusCol
If Sheet2.Range("A2") = "" Then
Set PasteCell = Sheet2.Range("A2")
Else
Set PasteCell = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
End If
If Status = "Next Month Feb" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell
Next Status
End Sub
I would appreciate your help with this issue.
I'm trying to create an excel spreadsheet (version 2019 and no ability to upgrade to 365) to keep track of appointments.
There are sheets for each month. And I need the appointments for future dates to show up in their respective monthly tab automatically (without disappearing from the original one).
I have attempted to create a VBA module but the problem I hit is that every time I ran the macro, the information copied over and over. Also, my "If status" only covered one month and I don't know how to cover more months and was trying to avoid creating a macro for each month.
Below is what I came up with from a tutorial I watched but like I said it came short for my needs. Is there a way to replicate the excel 365 recently introduced Filter formula? I think that's what I would need- but again, not sure
Sub CopyOverNextMonthMeetings()
Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Set StatusCol = Sheet1.Range("E2:E20")
For Each Status In StatusCol
If Sheet2.Range("A2") = "" Then
Set PasteCell = Sheet2.Range("A2")
Else
Set PasteCell = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
End If
If Status = "Next Month Feb" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell
Next Status
End Sub
I would appreciate your help with this issue.
