• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help creating macro to open a new tab, transfer Heathers, transfer require row and update the Date i

E

eddyrcabrera79

Member
Hi could you please help me with the creation of a macro? I’m working on the creation of an schedule.


Attached is an example, please note that original document will contain more columns and rows. I have an excel doc with a tab call “Calendar 2016”, and for columns (Name, Code, Cleaning Date, Completed Cleaning Date, Address). The cleaning services will happen once a year at the same location.


Cleaning date - Is the date where the cleaning will start

Completed Cleaning Date – Is the date where the Cleaning will end


This is what I require as the cleaning services will happen once a year in each location:


1 – I would like for example for the first row (House), as soon as I enter the (Completed Cleaning Date), a new sheet will be created with the name (Calendar 2017) all the heathers are transferred automatically.


2 – All the info in row 2 must be transferred to the first empty row in the sheet (Calendar 2017) except for the (Cleaning date and Completed Cleaning Date).

Cleaning Date (in Calendar 2017) will be the Completed Cleaning Date (in Calendar 2016 +1 year).

For example if in the sheet (Calendar 2016) the House Completed Cleaning Date is 31/03/2016 in the sheet (Calendar 2017) the Cleaning date will be 31/03/2017


3 – This will happen every year so the field (Completed Cleaning Date) will be empty until I input something manually and always it will open a new sheet


Please if you see a better way of doing this I’m open to new options


Thank you in advance
 

Attachments

I was able to find a code but I will require a bit of help. Everytime I put the word (Completed) in the column E, Sheet Calendar 2016, the entire row should go to the Sheet Calendar 2017. This code it only works if I start putting the word completed in order, one by one, if for example I put the first one and then the 4th , it will only transfer the first row


1 - I would like to fix this code, that as soon I type the word completed in any row in the calendar 2016, the complete row it will be transfer to the calendar 2017


2 – as soon as the row is transferred to the sheet Calendar 2017, that the Cleaning date is the 2016 Completed Cleaning date + one year


3 – the Completed Cleaning date column in the Calendar 2017 must be empty
 

Attachments

Hi Guys any help transferring the rows as per criteria "Completed" from sheet Calendar 2016 to sheet Calendar 2017?

also when rows are transferred I would like that the cleaning date column from sheets Calendar 2017 would be the Completed Cleaning Date from sheets Calendar 2016 plus 1 year

all the help is appreciated
 
See if this is how you wanted
Replace the current code in "Calendar 2016" code module with the code below.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    With Cells(1).CurrentRegion
        If Not Intersect(Target, .Columns("d").Offset(1).Resize(.Rows.Count - 1)) Is Nothing Then
            If Application.CountIf(.Columns("d"), "Completed") Then
                Application.ScreenUpdating = False
                Application.EnableEvents = False
                .AutoFilter 4, "Completed"
                .Offset(1).EntireRow.Copy _
                Sheets("Calendar 2017").Range("a" & Rows.Count).End(xlUp)(2)
                .Columns("c").Offset(1).ClearContents
                .Columns("d").Offset(1).Resize(.Rows.Count - 1).Value = "'" & Year(Date) + 1
                .AutoFilter
                Application.EnableEvents = True
                Application.ScreenUpdating = True
            End If
        End If
    End With
End Sub
 
Copy my code again and overwrite to current code.
Don't change "PrivateSub Worksheet_Change(ByVal Target As Range)" to
"Private Sub Worksheet_Activate()"

It execute each time you enter "Completed" in Col.D.
 
Hi Jindon,

I attached my file. I did as you requested but nothing happens. I used the original code you game me and I didn't modify it. I put it in the sheet (Calendar 2016) also tried to put it in a module, I typed "Completed" in the Column D but nothing happens automatically or when I run the Macro.

Could you please check my file?

Thank you so so so so much
 

Attachments

I found out the issue, the column where I type "Completed" is column E and not D. but I soon I type "Completed" none of the information gets transferred to the sheet (Calendar 2017), all the information in column D is replaced by the year 2017

Attached is the file

Thanks
 

Attachments

You must log in or register to reply here.
Back
Top