Excel Formula Solved/Closed

Question

Hello, 

I am looking for a formula or script that can help me with the following.

I am doing a spreadsheet for 19 different offices (on seperate tabs) for printers that are in the office (fields like company, make, model, agreement, lease cost, maintenance cost, copy cost, etc).  I want to pull some information automatically onto a summery page with only some info (eg company, make, model and office).

Can anyone help?  I would like to avoide double typing the information for all 19 offices onto a summery.

Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site  , http://wikisend.com/  , http://www.editgrid.com  etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too

rizvisa1 · Answer

Try the macro below

Note: Make sure that header on all sheet matches the headers on the summary report. There should not be extra spaces. In the sample the summary, the header had extra spaces trailing.

Sub CombineSheets()
Dim sConsolidatedSheet As String ' name of the consolidated sheet
Dim lConHeaderRow As Long ' Location of header row
Dim lConRow As Long ' number of current rows on consolidate sheet
Dim iConCol As Integer ' column number where data is to be copied

Dim Sheet As Variant ' name of the sheet being processed
Dim lSheetHeaderRow As Long ' location of the sheetheader on each sheet
Dim lSheetRow As Long ' number of of rows on sheet being processed
Dim iSheetCol As Integer ' column number where data is to be copied from

Dim ColumnsToCopy As Variant

    sConsolidatedSheet = "Summery"

    ColumnsToCopy = Array("Company", "Make", "Model", "Office")
    
    lConHeaderRow = 2
    lSheetHeaderRow = 2
    
    On Error Resume Next
        
        Sheets(sConsolidatedSheet).Select
    
    On Error GoTo 0
    
    If ActiveSheet.Name <> sConsolidatedSheet Then
    
        Sheets.Add
        ActiveSheet.Name = sConsolidatedSheet
        
        For Each Heading In ColumnsToCopy
            
            If Cells(lConHeaderRow, 1) = "" Then
                
                Cells(lConHeaderRow, 1) = Heading
            
            Else
            
                Cells(lConHeaderRow, Columns.Count).End(xlToLeft).Offset(0, 1) = Heading
            
            End If
            
        Next
        
    End If

    
    For Each Sheet In Sheets
    
        If Sheet.Name = sConsolidatedSheet Then GoTo Next_Sheet
        
        lSheetRow = 0
        lConRow = 0
        
        On Error Resume Next
            
            lConRow = Sheets(sConsolidatedSheet).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            lSheetRow = Sheet.Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        On Error GoTo 0
        
        If (lSheetRow <= lSheetHeaderRow) Then GoTo Next_Sheet
            
        For Each Heading In ColumnsToCopy
        
            iConCol = 0
            iSheetCol = 0
            
            iConCol = Sheets(sConsolidatedSheet).Cells.Find(Heading, Cells(1, 1), LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
            
            On Error Resume Next
            iSheetCol = Sheet.Cells.Find(Heading, Cells(1, 1), LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
            On Error GoTo 0
            
            If (iConCol > 0 And iSheetCol > 0) Then
            
                Sheet.Select
                Range(Cells(lSheetHeaderRow + 1, iSheetCol), Cells(lSheetRow, iSheetCol)).Copy
                
                Sheets(sConsolidatedSheet).Select
                
                Cells(lConRow, iConCol).Select
                
                Selection.PasteSpecial xlPasteValues
                
            ElseIf (iSheetCol = 0 And Heading = "Office") Then
            
                Sheets(sConsolidatedSheet).Select
                Range(Cells(lConRow, iConCol), Cells(lConRow + (lSheetRow - lSheetHeaderRow - 1), iConCol)).Value = Sheet.Name
            
            End If
            
        Next
        
        
Next_Sheet:
    
    Next
End Sub

Excel Formula

2 responses

Similar discussions

Subscribe To Our Newsletter!