Excel Formula
Solved/Closed
Related:
- Excel Formula
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Transfer data from one excel worksheet to another automatically formula - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 3, 2010 at 01:25 PM
Jun 3, 2010 at 01:25 PM
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.
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 3, 2010 at 10:40 AM
Jun 3, 2010 at 10:40 AM
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.
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.
Jun 4, 2010 at 04:25 AM