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.

 

[SkipVought]

You do NOT! They are posted there exclusively for mental reference, as a note to identify the table for the user (YOU). It can be COPIED and PASTED into a sheet and then PARSED, using Text to Columns, and at that point is a TABLE on sheet, to which you can then assign Named Ranges for use with the posted code.

Skip,

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

 

[shelby55]

Sorry Skip but I'm not understanding.

I'll back up and describe what I have and the process. Initially I wish to take the data for all the charts from another source and export it to a single sheet in Excel. Then I wish to take that single sheet and create single worksheets with the data entered into specific cells.

When the chart review is over and the reabstracters have entered their data in, I have a table of "mapping" and "master" where the mapping advises where the data from each single sheet is to consolidate into a master worksheet. The range of data and mapping source are in named ranges now.

So I'm going from one to many back to one. So I already have named ranges for mapping but just don't know how to include that in this piece to create a template per chart.

Thanks.


So, I already have a table with named ranges to be able to take the data per sheet and combine into one sheet.

 

[SkipVought]

You have a complex process, so a code example may be directed toward ONE aspect and ignore others.

In the posted example the Template get renamed. TILT! You not longer have a Template the next time around. But the issue we were dealing with was not creating sheets for a Template. Rather the MAPPING of data from the master to a sheet, row by row.

Is THAT the issue or are we still hung up on the From Thru Target logic that COPIES from the master and PASTES data into individual sheets?

Skip,

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

 

[shelby55]

Hi

Sorry but I'm still hung up on getting data from the master to single sheets. I already know how to get the data from many worksheets to one, now I'm working on getting one worksheet to many.

I realized in your code that renaming the template means it's no longer there to use again but don't know if there is a way around that.

Thanks.

 

[SkipVought]

Turn on your macro recorder and COPY the Template SHEET (not the data on the sheet) to create a new sheet in the 'image' of the Template.

Post back with your recorded code if you need help incorporating.

Skip,

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

 

[shelby55]

Hi Skip

Now I'm confused....where am I trying to incorporate this into? The code I get is:

Sub test()
Sheets("Template").Select
      Sheets("Template").Copy After:=Sheets(10)
End Sub

 

[SkipVought]

This goes into your loop at the Statement where

            'copy TEMPLATE SHEET HERE and Rename
            
  'take this comment off when your complete this code
            Set wsADD = Sheets("Template")

Use this code instead...

    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Set wsAdd = ActiveSheet

Skip,

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

 

[shelby55]

Thanks Skip!! I'll give it a whirl.

 

[shelby55]

Hi Skip

I'm still not getting this.

I have the "one" sheet that I wish to make "many" and it's called rawdata and the entire sheet is from A1 to HR201.

I created another worksheet called "RawData_Map" and created a column called "from" which is A, "target" which is B and "header names" which is C. I made named ranges of the all 3.

The column of from is =Match(C2,RawData!$1:$1,0)
target is the cell i.e. C8 that the data needs to go into on the template
C is the header row name like "chartno" that matches to the rawdata worksheet.

The code is:

Sub AbstractData()
Dim r As Range, wsAdd As Worksheet, t As Range


With Sheets("RawData_A")
For Each r In Range([A2], [A2].End(xlDown))

Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set wsAdd = ActiveSheet
wsAdd.Name = r.Value


For Each t In [From]
.Range(.Cells(r.Row, t.Value), .Cells(r.Row, t.Offset(0, 1).Value)).Copy
wsAdd.Range(t.Offset(0, 2).Value).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Next
Next

End With
End Sub

When I do this I get a run-time error 1004: Method "name" of object '_worksheet' failed which objected to the code:

 wsAdd.Name = r.value

When I took that out I still get the error with "application defined or object defined error on the line:

 .range(.cells(r.row, t.value), .cells(r.row, t.offset(0,1).value)).copy

Any help is greatly appreciated - thanks.

 

[SkipVought]

what is the value of r.Value assigned to the sheet name, when it errors?


Skip,

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

 

[shelby55]

It assigned the name of Template(2).

 

[SkipVought]

Is that value in Column A on RawData_A?

That's where r.Value comes from.

Skip,

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

 

[shelby55]

No...it's supposed to be picking case number which is on RawData column HR.

 

[SkipVought]

I asked what the value of [highlight]r.value[/highlight] was at the point of debug, NOT the value of the sheet name.

Skip,

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

 

[shelby55]

Hi

It doesn't appear to go past this line of code...though when I run it a worksheet appears with "Template(2)" but no data in it.

 

[SkipVought]

RUN to the error...
[tt]
1. hit the DEBUG button

2. hover your cursor over [highlight]r.value[/highlight] OR

2a highlight [highlight]r.value[/highlight], right-click in the highlighted area and select Add Watch OR

2b in the Immediate window, enter [highlight]?r.value[/highlight] and hit ENTER

3. post the value of [highlight]r.value[/highlight]
[/tt]

Skip,

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

 

[shelby55]

r.value = J5. This is actually the target cell in Template for CaseNo. In the rawdata, CaseNo is column HR.

 

[SkipVought]

The value [highlight]J5[/highlight] is not an invalid value for a sheet, UNLESS there is already a sheet named [highlight]J5[/highlight].

Skip,

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

 

[shelby55]

No there isn't already a sheet named J5 so why would it cause a problem? When I comment that out, the next error is run-time error 1004: application defined or object defined error.

The code is:

 .range(.cells(r.row, t.value), .cells(r.row, t.offset(0,1).value)).copy

Using the debug method the values for this are r.row = 2, t.value = 1, t.offset(0,1).value = C8

 

[SkipVought]

I set up a test with

1. 4 rows 98 columns in RawData_A.

2. Column A data is J1, J2, J3

Your AbstractData macro runs to completion, producing 3 sheet named, J1, J2 & J3.

On what row is J5?

Skip,

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