Click here for important information about Computing.net.
I would be very grateful if someone could help. I have a worksheet ("Master") which contains information on patients undergoing operations. The sheet has around 2000 lines and 56 columns (A:BD). Each row represents one patient and column C contains the name of the surgeon who carried out the operation I need to create separate worksheets for each of the 23 surgeons involved, each conatining a subset of the data on the "Master" worksheet. To give an example, I might have a worksheet called "Mr Smith" which would correspond to a surgeon's name in column C of "Master". On this sheet I need details of any patients who he operated on, but I do not need information from every column - probably only about half of them. They data fields are a mixture of text, dates and numbers.
Is it possible to write a macro that will be able to do this? I would be very grateful for any help, as I have never tried to write a macro before. I am using Excel 2003.
Thanks
I suggest you try this code in a backup copy of your workbook since macros can not be undone. This code will create a list of the surgeon's names in a new sheet ("MD List")and then create a sheet for each surgeon.
It will then copy each row that contains that name in the Master sheet to the specific sheet for that surgeon.
Since I can't see your spreadsheet from where I'm sitting, I had to make some assumptions.
On your Master sheet, I am assuming that you have column labels in Row 1 and that your data starts in Row 2.
I am assuming that you want the same column labels on each individual sheet.
You said that you didn't need to copy the entire row, but you didn't say what you really needed, so the code copies the entire row. This can be modified.
Let me know what you think
Option Explicit Sub MD_Sheets() Dim numShts, numItems, srcMD, mdSht, nxtRow As Integer Dim mdName As String 'Add a new worksheet for filtered list of MD Names ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = "MD List" 'Create a list of unique MD Names Sheets("Master").Columns("C:C").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("A1"), Unique:=True 'Count number of MD names so we know how many sheets to create numShts = WorksheetFunction.CountA(Sheets("MD List").Columns("A")) 'Create new sheets, named for each MD, add Column lables For mdSht = 2 To numShts ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = Sheets("MD List").Range("A" & mdSht) Sheets("Master").Rows(1).EntireRow.Copy _ Destination:=ActiveSheet.Range("A1") Next 'Count number of lines to be copied numItems = WorksheetFunction.CountA(Sheets("Master").Columns("C")) 'Copy each row to specific MD's sheet With Sheets("Master") For srcMD = 2 To numItems mdName = .Range("C" & srcMD) nxtRow = WorksheetFunction.CountA(Sheets(mdName).Columns("A")) + 1 .Range("C" & srcMD).EntireRow.Copy _ Destination:=Sheets(mdName).Range("A" & nxtRow) Next End With End SubPosting Tip: Before posting Data or VBA Code, read this How-To.
That is fantastic! It works perfectly. I am so grateful for your help. Regarding the columns, what I would ideally like to do is reorganise them in the individual sheets. The Master spreadsheet is sent to me each month from someone else - she uses it for billing and doesn't want to change the way it is put together. The output sheets, however, are to be sent to the individual doctors to check, and much of the original information is superfluous and would just confuse the picture. I want to keep these as simple as possible.
What I would like to do is to select a subset of the columns and re-order them, so that, for instance: A=>A; B=>ignored; C=>ignored; D=>G; E=>H; F=>B etc. Could you give me some sample code to insert and then I can extend it to cover all 20 or so columns that I need to transfer over.
Thanks again
Ad Choices