Excel Formula [Solved/Closed]

Report
-
 djb -
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.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thank you very much that worked brilliantly
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
https://authentification.site/files/22774801/Office_locations.xls
There is test data under the Aberdeen, Coventry and Hemel tabs. I has a Summary tab which I only need the company, make, model and office on. Under the individual offices I have more detailed information. I'm looking for a way to pull the information into the summary after it has been entered on the office tab. Many thanks for your help.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!