Many Worksheets to One 1

[shelby55]

(originally posted in another forum....Skip told me to post here)

Hello

I'm using Excel 2003.

I am in receipt of an Excel file where each worksheet is one patient's data (50 worksheets). The data per worksheet isn't across one row i.e. CMG = C6, Sex = C15 etc. but it is in the same location per worksheet so all worksheets have CMG at the location of C6.

What I want to do is have a single worksheet for all the data with each chart having all their data elements in a row.

To answer Skip's questions:

What CELLS map to what columns?
The data isn't all in one column for instance C contains most but there are also headings in "C" and also data included in D. Actually this step will be after I've figured out a solution but C4 would be A2 in the master yet to be created, C5 would be B2, C6 would be C2. But that is for the first record, for the next worksheet it will be A3, B3, and C3.

Is each sheet identically structured?
All worksheets are identical in terms of where the data is located on the worksheet - sex is always C15, CMG is always C6.

What is master sheet named?
There is not master worksheet...that is something I want to create to combine all the data.

Is this possible? Thanks.




What is master sheet named?

 

[SkipVought]

What CELLS map to what columns?
The data isn't all in one column for instance C contains most but there are also headings in "C" and also data included in D. Actually this step will be after I've figured out a solution but C4 would be A2 in the master yet to be created, C5 would be B2, C6 would be C2. But that is for the first record, for the next worksheet it will be A3, B3, and C3.

"The data isn't all in one column..."

Nothing in my question assumed that it is.

I need to know...
[tt]
Source Master COL
C5 2
C6 3
....
[/tt]



Skip,

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

 

[randy700]

I found this on line but can't remember where:

Sub CombineWorksheets()
Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(3).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 3 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A2:M5000").Select
        ' Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        ' Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next

End Sub

Randy

 

[SkipVought]

randy,

does not really answer the OP's specific question.

Skip,

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

 

[shelby55]

Randy: Thanks I'll try this out.

Skip: I thought I had identified what you were asking...I was just hoping to find out what route to take without providing every data element and mapping it. You are correct with the 2 noted.

 

[SkipVought]

Sub Consolidate()
'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")
    
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "Master", "Mapping"
                Case Else
                    lRow = wsMSTR.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [Source]
                        wsMSTR.Cells(lRow, r.Offset(0, 1).Value) = .Range(r.Value)
                    Next
            End Select
        End With
    Next
End Sub

Skip,

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

 

[shelby55]

Hi

Randy this is looking at rows of data but I'm trying to transpose columns into rows as well as have each new row of data on the combined sheet to drop to the next line which I don't see that this does....but correct me if I'm wrong.

Thanks.

 

[shelby55]

Skip

Sorry but there was cross posting so saw yours after I posted....this sounds promising and I'll try it out. Thanks.

 

[shelby55]

HI Skip

Sorry but I'm assuming that I format the mapping table to be as I want it i.e.

Chartno AcctNo AdmDate SepDate
C3 C4 C5 C6

I'm assuming the code you provided goes in a module but how to get it to fire? Thanks.

 

[SkipVought]

Your assumption is incorrect
[tt]
Source MasterCOL
C3 Chartno
C4 AcctNo
C5 AdmDate
C6 SepDate
[/tt]
where the COLUMN NUMBER is to be substituted for the string value.

I'm assuming the code you provided goes in a module but how to get it to fire?

It should be in the macro list. Otherwise, insert a BUTTON on your sheet and assign this macro.

Skip,

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

 

[shelby55]

Thanks Skip. I ran the macro but it stopped at

For Each r In [Source]

Thanks.

 

[SkipVought]

Did you NAME the range in accordance with the HEADINGS in Mapping?

You should see Source and MasterCOL in the NAME BOX.

Skip,

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

 

[shelby55]

No...sorry so one named range for the data from A1 to FS2? Or does each cell have to be named (so in this case 175 named ranges)? Have you specified a name in your code or can I indicate anything? Thanks.

 

[SkipVought]

I am referring to the Mapping sheet, having TWO columns of data: hence TWO named ranges, one of which is Source.

Skip,

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

 

[shelby55]

I created the named range of "source" for the range identified and now it stops at

wsMSTR.Cells(lRow, r.Offset(0, 1).Value) = .Range(r.Value)

 

[SkipVought]

please copy n paste the table from Mapping.

Skip,

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

 

[shelby55]

Sorry Skip you lost me....right now the data in mapping is

Chartno AcctNo AdmDate SepDate
C3 C4 C5 C6

or
Source MasterCOL
C3 Chartno
C4 AcctNo
C5 AdmDate
C6 SepDate

Or I guess it really doesn't matter because you want two named ranges one will be for the source and the second will be for MasterCOL (both named ranges as such)?

Thanks.

 

[SkipVought]

Where are the COLUMN NUMBERS that I have mentioned repeatedly?

Skip,

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

 

[SkipVought]

OK, use this formula in column C
[tt]
Source MasterCOL
C3 Chartno =MATCH(B2,Master!$1:$1,0)
C4 AcctNo 4
C5 AdmDate 5
C6 SepDate 6
[/tt]
and copy down.

Then change this statement...

wsMSTR.Cells(lRow, r.Offset(0, [b][red]2[/red][/b]).Value) = .Range(r.Value)

Skip,

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

 

[shelby55]

Okay...but what now are the named ranges? I'm sorry but what do you mean by column numbers?? So A = 1 and B =2 etc???