From Single Excel Worksheet to Many 1

[shelby55]

Hello

I am using Excel 2002.

I have a worksheet of data where one line of information is a single patient visit.

What I would like to do is take that data and place each patient visit in its own worksheet with the worksheet tab reflecting the case number. I require each worksheet to have the data in the same location i.e. A2 will indicate "chart number" and B2 will have the chart number.

I received help from this forum previously in doing the reverse i.e. many worksheets to create one by having a mapping worksheet indicating the cell title and cell location, naming the ranges and writing a macro to the new worksheet which has the column headers enter. The macro code was:

Sub Consolidate_OrigA()
'this assumes
' Master sheet for combining data from all other sheets
' HEADINGS in Master in Row 1 starting in column A
' Mapping sheet with NAMED RANGES
'  Source & MasterCOL
    Dim ws As Worksheet, r As Range, wsMSTR As Worksheet, lRow As Long
    
    Set wsMSTR = Sheets("Master_OrigA")
    
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "Master_OrigA", "Mapping_OrigA"
                Case Else
                    lRow = wsMSTR.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [SourceOrigA]
                        wsMSTR.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
                    Next
            End Select
        End With
    Next
End Sub

Thanks for any and all assistance.

 

[shelby55]

In the template, J5 is Case Number. In RawData column J5 is InstTo which doesn't always have data i.e. not everyone is tranferred elsewhere. In J5 RawData the value is 54666.

 

[SkipVought]

Is there a HARD VALUE in Column A that is "J5" OR is there a REFERENCE in column A, like
[tt]
=J5
[/tt]
and the VALUE is 54666 or something?




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}