Hi Susan
Thanks for the code, but it did not meet my requirement...

Actually i have four sheets and they are 'Master', 'Under Treatment',
'Critical', 'Died'

Master sheet, is the sheet which contain all the informations as per sheet
names ('Under Treatment', 'Critical', 'Died'), and it is updated on daily
bases, my requirement starts here, as an when the master sheet is updated,
after that when i run macro the details which are given in master sheets need
to be updated to the respective sheets as per the sheet names.

For example : today if i update my master sheet only about Died details

Sheet Name: Master
Status Date Patient ID Docter Name Region
Died 6/25/2008 45677 Pradeep DK

Answer
after runing macro the above information, need to be copied from the master
sheet and put it in 'Died' Sheet

Sheet Name: Died
Status Date Patient ID Docter Name Region
Died 6/25/2008 45677 Pradeep DK

the logic here is i have 50 sheets, every day i have to copy the information
of 50 sheets from one master sheet, i just want to avoid it.

Please help me.....





"Susan" wrote:

found one error (was skipping last line) & wrapped those 2 lines......
try this one instead.
============================
Option Explicit

Sub Ranjit()

Dim myLastRow As Long
Dim mySheet As Worksheet
Dim WS As Worksheet
Dim mySheetName As String
Dim myShtRow As Long
Dim myRange As Range
Dim myOtherRange As Range
Dim myCell As Range

Set WS = Worksheets("Master")
myLastRow = WS.Cells(20000, 1).End(xlUp).Row
Set myCell = WS.Range("a2")

Do Until myCell.Row = myLastRow + 1
If myCell.Offset(0, 1).Value = Date Then
Set myRange = WS.Range("a" & myCell.Row _
& ":e" & myCell.Row)
mySheetName = myCell.Text
Set mySheet = Worksheets(mySheetName)
myShtRow = mySheet.Cells(20000, 1).End(xlUp).Row + 1
Set myOtherRange = mySheet.Range("a" _
& myShtRow & ":e" & myShtRow)
myRange.Copy Destination:=myOtherRange
End If
Set myCell = myCell.Offset(1, 0)
Loop

End Sub
========================
susan


On Jun 25, 2:00 pm, Susan wrote:
well, no one else has tried, so i'll give it a go. please save your
work before running this macro. i did not make it an automatic
worksheet_change as you requested, because it would be running
constantly & would not work correctly, i don't think. instead, i made
it as a stand-alone that would be run once or twice a day.

watch out for the two longest lines (setting the ranges). they will
definitely wrap the text in the newsgroup reader! you will have to un-
wrap them. i wasn't sure how to break them up in the coding.
=======================
Option Explicit

Sub Ranjit()

Dim myLastRow As Long
Dim mySheet As Worksheet
Dim WS As Worksheet
Dim mySheetName As String
Dim myShtRow As Long
Dim myRange As Range
Dim myOtherRange As Range
Dim myCell As Range

Set WS = Worksheets("Master")
myLastRow = WS.Cells(20000, 1).End(xlUp).Row
Set myCell = WS.Range("a2")

Do Until myCell.Row = myLastRow
If myCell.Offset(0, 1).Value = Date Then
Set myRange = WS.Range("a" & myCell.Row & ":e" & myCell.Row)
'unwrap me
mySheetName = myCell.Text
Set mySheet = Worksheets(mySheetName)
myShtRow = mySheet.Cells(20000, 1).End(xlUp).Row + 1
Set myOtherRange = mySheet.Range("a" & myShtRow & ":e" &
myShtRow) 'unwrap me
myRange.Copy Destination:=myOtherRange
End If
Set myCell = myCell.Offset(1, 0)
Loop

End Sub
====================
hope it at least gets you started!
:)
susan

On Jun 25, 5:35 am, Ranjit kurian



wrote:
Hi

I have a master sheet in excel which contain all the below details as per
sheet names('status' column are the sheet names)

Status Date Patient ID Docter Name Region
Critical 6/25/2008 34567 Suhas IE
Died 6/25/2008 12345 Pradeep US

This master sheet is updated on daily bases, new recordes will been added on
daily, i need a macro so that when ever the new recordes are added to the
master sheet , all the other sheets similutaneous need to be filled example:

if the Status column is Critical then what ever details is update next to
that column need to filled in Critical Sheet.......

The macro should match the sheet name and mastersheet data , then it should
copy the related details to that particular sheet- Hide quoted text -

- Show quoted text -