Data need to be copied as per sheet name

[Ranjit kurian]

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

 

[Susan]

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

 

[Susan]

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

 

[Ranjit kurian]

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]

this sub should work regardless of how many sheets you have...... from
what you explain the macro i wrote will do what you want; it will copy
the data from the master sheet to the sub-sheet.
but you are saying you want the information UPDATED, which is
different than just copied/listed; the same person may be on the
master sheet several days, with a different status each day, and you
only want them to appear once, under the now-correct status sheet.
if that's what you want, i'm sorry, i don't know how to do that. the
only thing i can think of is another round of macro that strips out
duplicates based on patient ID & date & just leaves the most recent
entry.
susan