Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data need to be copied as per sheet name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data need to be copied as per sheet name
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data need to be copied as per sheet name
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data need to be copied as per sheet name
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data need to be copied as per sheet name
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 On Jun 26, 8:36*am, Ranjit kurian wrote: 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 -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info) | Excel Programming | |||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info) | Excel Programming | |||
how to update a worksheet and have data copied to another sheet? | Excel Worksheet Functions | |||
Combobox data accidentally copied on different sheet | Excel Programming | |||
Copied empty cells, give sorting problem in Data sheet | Excel Programming |