Help with a 'view sheet'

[ecourt]

hey guys,
I have a excel spreadsheet that has 28 sheets.
It's for documenting a phone-switch.

so each shelf has it's own sheet,
each sheet has the shelf name at a1
then has the following columns:
slot, port, usage, carrier, circuit-id, ds3-id, ds3-port

I need to create ONE sheet from the 28 sheets that is a copy of all the data from the 28 sheets, that has ds3-id, then ds3-port (sorts on those 2 fields) then the other information in the sheets...
and of course, it needs to keep itself updated.

any help is appreciated. I'm new at this excel macro stuff. I've written a few little macros, but nothing like this.

Thanks

 

[Joe4]

Welcome to the Board!

I have been watching this thread all day to see if there have been any responses. Since there have not been, I will toss in my two cents, for whatever that's worth.

What you have described sounds like more of a "database" model than a "spreadsheet" model. As such, a database program, such as Access, would probably be the better tool to use for something like this. You have just one table of data, but different "views" or "queries" that shows you different sheets/scenarios. So you can display it however you like, but all you data is stored in one central location behind the scenes, so you do not have to worry about updating it on multiple sheets/tables/etc.

 

[ecourt]

Welcome to the Board!

What you have described sounds like more of a "database" model than a "spreadsheet" model. As such, a database program, such as Access, would probably be the better tool to use for something like this. You have just one table of data, but different "views" or "queries" that shows you different sheets/scenarios. So you can display it however you like, but all you data is stored in one central location behind the scenes, so you do not have to worry about updating it on multiple sheets/tables/etc.

I don't disagree...
problem is, company policy is I can't use access. They don't even load it on PC's.

In the past, we've always just had 2 docs.. 1 the DS3 people used, and one WE use (the switch guys) I was hoping to be able to merge them into one doc.

I still think I can get it to work, just not sure where to start,
I know I'm going to have to loop through each sheet, that much I can do, the copying, I can do, but how to paste, without overwriting...
Once the data is in the sheet, it's simple to sort it.

 

[Michael M]

Hi
Have a look at this code kindly provided by Jerry Beaucaire
I think it will provide you with what you need however, you would have to "update" it periodically by running the macro on a regular basis.
Obviously Sheet, column and cell references would have to be modified.

Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
'Data is sorted by a specific column name
Dim cs As Worksheet, ws As Worksheet
Dim LR As Long, NR As Long, sCol As Long
Dim sName As Boolean, SortStr As String
Application.ScreenUpdating = False

'From the headers in data sheets, enter the column title to sort by when finished
SortStr = "Invoice #"

'Add consolidation sheet if needed
If Not Evaluate("ISREF(Consolidate!A1)") Then _
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Consolidate"

'Option to add sheet names to consolidation report
sName = MsgBox("Add sheet names to consolidation report?", vbYesNo + vbQuestion) = vbYes

'Setup
Set cs = Sheets("Consolidate")
cs.Cells.ClearContents
NR = 1

'Process each data sheet
    For Each ws In Worksheets
        If ws.Name <> "Consolidate" Then
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            'customize this section to copy what you need
            If NR = 1 Then      'copy titles and data
                ws.Range("A1:BB" & LR).Copy
            Else                'copy data only
                ws.Range("A2:BB" & LR).Copy
            End If
            
            If sName Then       'paste and add sheet names if required
                cs.Range("B" & NR).PasteSpecial xlPasteValues
                cs.Range("A" & NR, cs.Range("B" & cs.Rows.Count).End(xlUp).Offset(0, -1)) = ws.Name
            Else
                cs.Range("A" & NR).PasteSpecial xlPasteValues
            End If
            
            NR = cs.Range("A" & cs.Rows.Count).End(xlUp).Row + 1
        End If
    Next ws

'Sort
    LR = cs.Range("A" & cs.Rows.Count).End(xlUp).Row
    sCol = Cells.Find(SortStr, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    cs.Range("A1:BB" & LR).Sort Key1:=cs.Cells(2, sCol + (IIf(sName, 1, 0))), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

'Cleanup
    If sName Then [A1] = "Sheet"
    Rows(1).Font.Bold = True
    Cells.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    cs.Activate
    Range("A1").Select
End Sub

 

[ecourt]

That's got some good stuff in it, should get me started...

The manual update should be no problem, because I already have a 'start page' that has links to other macros, and some shortcuts on it.. I'll just put a link there, and have it run every time someone clicks on it.

Thanks, I'll post back if I have more questions.