No experience!! Help!

[cegley]

I need a Visual Basic written for a workbook I have created for my employees.

What I want to happen is for a row from one worksheet to be automatically transferred to a different sheet after the date in a column passes.

Worksheet with the information: "Current Students"
Column with Date: "O"
Column "P" is the last with any entry
Worksheet I want the data transferred to: "Fall 2016 Term Date"

The number of rows in the "Current Students" tab is constantly changing as students come and go during the semester, I also want this to be a constantly running VB, or it updates upon start-up of the worksheet.

I have approximately no experience with programming, and am going to need step by step instructions if I need to rename any columns or copy and paste a code into anywhere other that VB. My knowledge of Excel is pretty average.

Thank you so much! I have spent HOURS trying to edit codes to work for my worksheet. I thought I had one that worked really well, but then it started to move the row any time I entered data into the "O" column.
I have Excel 2013 on Windows 10.

 

[cegley]

http://www.mrexcel.com/forum/excel-...eet-into-another-worksheet-same-workbook.html

this is the page I got the code I am currently using from, but it moves dates anytime I enter them, not after the date has passed.

 

[manu197a]

This code will analyze the dates in the defined column for all the rows in the defined sheet. You can modifiy the code's parameters for it to do what you need. Check the value of these variables in the code:
Original_Sheet
Second_Sheet
Column_w_Date
Last_Column
First_Row
Limit_Date

The code will check if the date in the Column_w_Date is greater than the Limit_Date. If it is, the row will be moved from the Original_Sheet to the Second_Sheet. Only the columns 1 to Last_Column will be moved.

Let me know if this works fine for you.

Sub Move_Registries_To_Worksheet()

Dim Original_Sheet As String
Dim Second_Sheet As String
Dim Limit_Date As Date

Dim Column_w_Date As Integer
Dim Last_Column As Integer
Dim First_Row As Integer
Dim Last_Row As Integer

''''''''''''''''''''''''''''''''''''''''''''''''''''
'THESE ARE PARAMETERS YOU HAVE TO DEFINE

'The name of the sheet where the original data is:
Original_Sheet = "Current Students"

'The name of the sheet where the data will be moved:
Second_Sheet = "Fall 2016 Term Date"

'The column that contains the date to be analyzed:
'A = 1, B = 2, ..., O = 15
Column_w_Date = 15

'Last column with data in the original sheet:
'A = 1, B = 2, ..., O = 15
Last_Column = 16

'The first row with data (after the headers and anything else in the sheet):
First_Row = 2

'If the date in the column above is greater than this date, the data will be moved:
'Year, Month, Day
Limit_Date = DateSerial(2016, 7, 1)
''''''''''''''''''''''''''''''''''''''''''''''''''''

Sheets(Original_Sheet).AutoFilterMode = False
Sheets(Second_Sheet).AutoFilterMode = False

Last_Row = Sheets(Original_Sheet).Cells(Rows.Count, Column_w_Date).End(xlUp).Row

'If there are no rows with valid data, the macro ends
If Last_Row < First_Row Then
    MsgBox "There's no valid data in the sheet."
    Exit Sub
End If

i = First_Row
Do While i <= Last_Row
    If IsDate(Sheets(Original_Sheet).Cells(i, Column_w_Date)) Then
        If Sheets(Original_Sheet).Cells(i, Column_w_Date) > Limit_Date Then
            Sheets(Original_Sheet).Range(Cells(i, 1), Cells(i, Last_Column)).Copy _
                Sheets(Second_Sheet).Cells(Sheets(Second_Sheet).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
            Cells(i, 1).EntireRow.Delete
        Else
            i = i + 1
        End If
    
    Else
        i = i + 1
    End If
Loop

End Sub

 

[cegley]

I copied and pasted the code into the VB for sheet 1. Did I need to put it in for sheet 2 as well? When I type a date in the past into column "O" it is not moving over. I'm not sure what I did wrong. Do the columns and rows need to have defined names?

 

[manu197a]

You don't need to change the column names. The code does not run automatically. You can run it in the Developer menu, macros, and selecting "Move_registries_To_Worksheet". You can also define a short path for the macro (for example: Ctrl + Alt + Q). All this is in the Developer menu.

My recommendation is for you is to run the macro only when you need it (for example after you or someone else finnishes adding the students' information). If you program the macro to run automatically each time you activate the worksheet or you make a change in a cell, you might get confusing results.

 

[cegley]

It worked when I did this except, it moved the dates in the future instead of the past. (8/22/2016)

If the date in the column above is greater than this date, the data will be moved:
'Year, Month, Day
Limit_Date = DateSerial(2016, 7, 1)
''''''''''''''''''''''''''''''''''''''''''''''''''

Should this part say "less than this date"?
Also thank you very much for your help!!!

 

[cegley]

I got it to work! Thank you so much! I changed it to less than and then later on I just had to change it from > to <

 

[manu197a]

Nice!
Remember to change all the parameters in order to achieve exactly what you need.