Using a macro to select and move data (Excel)

Microsoft Excel 2003 (full)
November 15, 2010 at 08:49:38
Specs: Windows XP, Intel Core2 3GB RAM
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.


See More: Using a macro to select and move data (Excel)

Report •

November 15, 2010 at 17:19:11
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 _
'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)
    End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

November 16, 2010 at 07:12:58
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

Report •

Related Solutions

Ask Question