Copy a row to a separate sheet with a condition

Would you mind put some data in your sheets so we see what you want to be copied? I suppose you only want to copy rows starting at row 9 and below.
Also, what do you mean by "the code should be applied to all 52 sheets"? Can you detail your process a little bit more?
Regards

here is a macro you could try. It could be run from any sheet and will copy data on a sheet's name defined by the actual sheet name (which should be a date in a format suitable for the DateValue function) with 7 days added to it.

Please note that I had to reposition a cell in column B into column C because the macro uses a function to locate the last entry in column B and that this TITLE was in the way. You can place it where you want except in column B.

Public Sub Transfer_Data()
Dim WS_Name As String, wsSheet As Worksheet
Dim D_ate As Date, New_Date As Date, WS2_Name As String, C_ell As Range
Application.ScreenUpdating = False
WS_Name = ActiveSheet.Name
D_ate = DateValue(WS_Name)
'Next sheet name will be 7 days later
New_Date = D_ate + 7
'Here is where you define the new sheet name
WS2_Name = Day(New_Date) & " " & Left(MonthName(Month(New_Date)), 3) & " " & Year(New_Date)
'Checking if next sheet exists
On Error Resume Next
Set wsSheet = Sheets(WS2_Name)
On Error GoTo 0
If wsSheet Is Nothing Then
   MsgBox "Sheet " & WS2_Name & " does NOT exist"
   Application.ScreenUpdating = True
   Exit Sub
End If
'Starts copying data to next sheet
For Each C_ell In Range("B9", Cells(Rows.Count, 2).End(xlUp))
   If C_ell.Offset(0, 9) = "Still in Care" Then
      C_ell.Resize(1, 8).Select
      Selection.Copy
      Sheets(WS2_Name).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial
      Sheets(WS2_Name).Cells(Rows.Count, 2).End(xlUp).Offset(0, 8) = "Still in Care"
      C_ell.Resize(1, 1).Select
   End If
Next
Application.ScreenUpdating = True
End Sub

Hope this help

Hi,
First thing is to put this macro in a Module. Actually it is at the sheet level and this means it is not available to the other sheets. You want to place it in a Module (any one) so that you will be able to call it form anywhere in the workbook. DO NOT FORGET TO ERASE IT AT THE SHEET LEVEL.

Secondly, the macro was modified and you made some mistake doing it. Here is a version that works in your workbook:

Public Sub Transfer_Data()
Dim WS_Name As String, wsSheet As Worksheet
Dim D_ate As Date, New_Date As Date, WS2_Name As String, C_ell As Range
Application.ScreenUpdating = False
WS_Name = ActiveSheet.Name
D_ate = DateValue(WS_Name)
New_Date = D_ate + 7
WS2_Name = Day(New_Date) & " " & Left(MonthName(Month(New_Date)), 3) & " " & Year(New_Date)

For Each C_ell In Range("B9", Cells(Rows.Count, 2).End(xlUp))
  If C_ell.Offset(0, 9) = "Still In Care" Then
      C_ell.Resize(1, 8).Select
      Selection.Copy
      Sheets(WS2_Name).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial
      Sheets(WS2_Name).Cells(Rows.Count, 2).End(xlUp).Offset(0, 8) = "Still In Care"
      C_ell.Resize(1, 1).Select
  End If
Next
Application.ScreenUpdating = True
End Sub

There were 2 main errors:
1. The variable name WS2_Name was changed to WS2_ "14 jan 2014"
2. there was an "End IF" statement left when you deleted some part of the macro.

I saw you deleted the part that is checking if the next week sheet exists. It's up to you to do so but if for some reason the macro does not find the next week sheet, you'll get an error meesage and it will not as obvious as the message box the macro was displaying when it happens. I'd rather leave it there.
Hope this helps.

Here is your workbook with the macro in Module1. It workslike this:
When in any sheet, you run the macro named Transfer_Data which will take the sheet name, convert it in a date, add 7 days to this data and come up with the next sheet name. It then scan all rows in the active sheet and copy those that have "Still In Care" in column K to the next sheet as required.
Hope this will work for you. If not, then ask again for help.
Regards

Check if your Excel is configured to accept to run macros. By default, it is not. You have to change it in the Files/Options/Trust Center/Trust Center Settings/Macro Settings menu.