• 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.

Re: Copy a row to a separate sheet with a condition

malitec

New Member
Hi guys

Requesting your guidance to copy the cells (B:I and K) in each row in sheet " 7 Jan 14 " to cells B:J in sheet " 14 Jan 14 " when the text in K is " Still In Care", which is the condition. Please note the condition in "K" in sheet " 7 Jan 14 " is not copied to "K" in sheet " 14 Jan 14 " instead it should be copied in to cell J instead.

I have uploaded a workbook with data. As an example I have manually copied the data from sheet "7 Jan 2014" and paste it in "14 Jan 2014" just to show you how it will look like. You will notice the text in cell "K" of "07 Jan 2014" worksheet is copied to cell "J" in "14 Jan 2014". Rest of the data from "04 Jan 2014" are copied in to the same cells in "14 Jan 2014".

Looking forward for your valuable guidance.

Thanks

Malitec
 

Attachments

  • VO52weekstemplatetrial.xlsm
    470.3 KB · Views: 14
Hi ,

Can you confirm whether the requirement you have posted will pertain only to the the two tabs you have mentioned , or will you be extending this to the other tabs also ?

If possible , please list down the complete requirement right at this stage , so that if any code is developed , it will not need to be modified again.

Secondly , what is supposed to happen when the text Still in Care changes at a later date ? Or will it always remain Still in Care ?

Narayan
 
Hi,

Try below code.

Code:
Option Explicit

Sub copyRow()

Dim dws As Worksheet
Dim sws As Worksheet
Dim cell As Range

Set sws = ThisWorkbook.Sheets("7 Jan 2014")
Set dws = ThisWorkbook.Sheets("14 Jan 2014")

For Each cell In sws.Range("K9:K23")
    If cell.Value = "Still In Care" Then
        sws.Range(sws.Cells(cell.Row, 2), sws.Cells(cell.Row, 9)).Copy dws.Range("B" & Cells(Rows.Count, 2).End(xlUp).Row).Offset(1, 0)
        dws.Range("J" & Cells(Rows.Count, 10).End(xlUp).Row).Offset(1, 0) = cell
    End If
Next

End Sub

Regards,
 
Hi ,

Can you confirm whether the requirement you have posted will pertain only to the the two tabs you have mentioned , or will you be extending this to the other tabs also ?

If possible , please list down the complete requirement right at this stage , so that if any code is developed , it will not need to be modified again.

Secondly , what is supposed to happen when the text Still in Care changes at a later date ? Or will it always remain Still in Care ?

Narayan
Hi Narayan

The macro should be applied to all 52 sheets. Please note there are two other sheets which shouldn't be included in the macro {Sheet 'Hyperlinks' and 'Data'(Hidden sheet)}

Text 'Still In Care' can change when client exits and the Still In Care text will be replaced with a date.I need to copy only the row that comes with the text 'Still In Care" in column K to the next sheet. Sheets in the workbook represents the 52 weeks of the year.

My request is when ever Still In Care in recorded in column K of any sheets, the data should be copied to the next sheet only.

Thank you
 
Hi,

Try below code.

Code:
Option Explicit

Sub copyRow()

Dim dws As Worksheet
Dim sws As Worksheet
Dim cell As Range

Set sws = ThisWorkbook.Sheets("7 Jan 2014")
Set dws = ThisWorkbook.Sheets("14 Jan 2014")

For Each cell In sws.Range("K9:K23")
    If cell.Value = "Still In Care" Then
        sws.Range(sws.Cells(cell.Row, 2), sws.Cells(cell.Row, 9)).Copy dws.Range("B" & Cells(Rows.Count, 2).End(xlUp).Row).Offset(1, 0)
        dws.Range("J" & Cells(Rows.Count, 10).End(xlUp).Row).Offset(1, 0) = cell
    End If
Next

End Sub

Regards,
Hi Somendra

Thank you for your prompt reply. I think the macro you have developed applies only to two sheets but I am looking for a macro that can be applied for all 52 sheets.

A module based macro will be more suitable so that if I change the name of the sheets it will not affect the function of the macro. As an example; when I prepare the 52 week workbook for year 2015, I can use the macro if it is a module based one.What is your valuable opinion about that?

For other details please refer to the reply I have just posted to Mr Narayan's post.

Thank you
 
Hi ,

Thanks for the clarifications ; a few doubts still remain :

1. When will you modify a sheet cell in column K ? Will the date on which you enter the text Still in Care in any cell in column K on any sheet have any relationship to today's date ?

What I mean is , we are now on October 19 or 20 , 2014 ; will you enter the text on a sheet tab 11 Feb 2014 ?

2. When do you want this data transfer to take place ? Do you want that the moment the cell is populated with the text the data should be transferred ?

There is a column of data after column K , so if the data transfer is done when the cell in column K is populated , at which point in time , the cell in column L may or may not have data , this data will not be transferred. Is this OK ?

3. What happens if a cell is later on modified to replace the text with some date ? Will this data also need to be propagated either backward or forward ?

Suppose you have an entry from 21 Jan 2014 till 25 Feb 2014 ; now will the text be changed to a date in the sheet tab 25 Feb 2014 ? Will this change be carried over to any other sheet tab ?

Narayan
 
Hi Narayan

First of all my apologies for the delayed reply. I will answer your questions as per the number.

1. The excel sheet is a report we send out every week on a Tuesday and the report is from the Tuesday of the week before to the current one. Based on that I have prepared 52 sheets representing 52 weeks of the year.Still in Care in any cell in column K on any sheet does not have any relation to today's date.

2. I want to transfer the data the moment any cell in column K is populated with the text 'Still In Care'.

I do not want the data in column L to transfer.

3.Please remember the when any cell in K is entered with text 'Still In Care', data from column B to I only are transferred. Still In care from any cell in column K is transferred to column J in the next sheet.This shows that the client was with us when the reporting period ends and the same client should be registered for the next reporting period.

Also if any cells in column K is modified with a date, I can manually erase that transferred data from the next sheet.

Hope I have answered your queries.

Thanks

malitec
 
Hi ,

See the file now.

Try out the code in this file with a variety of data entries , and only when you are sure everything works the way you want it to , you can copy and paste the code in your working file.

Narayan
 

Attachments

  • VO52weekstemplatetrial.xlsm
    489.2 KB · Views: 9
Back
Top