loop through all tabs, finding headers from a list and compile the information

[nikk]

HI,

I am stuck - I have been trying different things with this. I have a macro that compiles the monthly statements from a folder into one workbook. These statements have similar information but with different headers as they come from different sources (i.e. Purchase Order Number might be PO# or P.O. Num, etc.). I am going to create a list of possible examples for each header. I want to create a macro that goes through each tab finds the header from the list and pastes the information below the header in one tab:

The thing is that not all statements will have all the columns - only the invoice # would be there 100% of the time.

Any ideas/suggestions?

Thank you

 

[Norie]

In the second image are the values in the first row the names for the headers in the consolidate sheet and the values in the rest of the rows the possible headers for that field in the other sheets?

For example, in the consolidated sheet you have a field/column named 'Invoices' and the possible names for that in the other sheets are 'Order No', 'Invoice No' and 'Reference'?

 

[DanteAmor]

I did not understand what you need.
You could describe step by step what you want to do.
Something like this:
Step 1. The macro should read column X of sheet Y.
Step 2. The macro should look for the data W on the "bla bla" sheet.
Step 3. Etc.

To explain better use real examples, you can paste here ranges of your sheets with data, check how to do it:
Upload an excel range:
XL2BB - Excel Range to BBCode

 

[nikk]

Yes Norie that is correct.

 

[nikk]

I did not understand what you need.
You could describe step by step what you want to do.
Something like this:
Step 1. The macro should read column X of sheet Y.
Step 2. The macro should look for the data W on the "bla bla" sheet.
Step 3. Etc.

To explain better use real examples, you can paste here ranges of your sheets with data, check how to do it:
Upload an excel range:
XL2BB - Excel Range to BBCode
[/QUOTE

Yes, may be I am over my head with this one. I somewhat know to do each part separately but piecing them together is mission impossible.
I have a macro that takes all the statements for the month and puts them in a file. I want to extend it:

1. To create arrays from the "Headers" tab from each column - these lists will increase as I add new names;
2. Go to each tab (statement) check the headings (which are not necessarily the first row - let's say within the first 10 rows) and check from the lists in "Headers" if the heading exist copy the information below it - there might be empty cells.
3. Paste the information in the Consolidated tab under the correct header (these are not going to change) which is as Noriw pointed out the first row in the Headers tab. Only the Invoice # column will exist in 100% of the time (which again might have a couple of cells with empty values for some statements) in the statements tabs so xldown will not work for the rest of the columns.
4. It would be nice if column A on the Consolidated statement contains the name of the tab where the information is coming from.

Headers tab:

Consolidated Tab:

Sample of statements:

 

[DanteAmor]

Hi @nikk, By the way, welcome to the forum!

None of the headings on the "statement" sheet match the texts on the "Headers" sheet.
We could keep guessing, but it would be easier to understand what you require if the examples are somehow "real", it would also be convenient for us to fill out the "Consolidated" sheet with the expected result.

I help you a little with the examples and you help me if they are not correct.
Note: The sequence of headings in row 1 of the "Headers" sheet must be the same as the "Consolidate" sheet. Look at the examples:

Book1
	A	B	C
1	Po #	Invoice #	Invoice Date
2	Order No	Ref	Doc Amount
3	Invoice No	Document number	Invoice Amount
4	Reference	Assignment
5	Ref.key 1
6	P.O. No.
7	Purchase Order
Headers

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4	customer job	transaction type	date	Document number	P.O. No.	Due Date	age	Open balance
5	508
6		invoice	08/07/2019	inv33002	4280-01	22/08/2019	39	18000
7		invoice	08/07/2019	inv33003	4276-01	22/08/2019	39	1071
8
9
Statement 9-30

Book1
	A	B	C	D	E	F	G	H
1	Order No	customer job	Assignment	transaction type	date	Due Date	age	Open balance
2		508
3	4280-01b		inv33002-10	invoice	08/07/2019	22/08/2019	39	18000
4	4276-01b		inv33003-10	invoice	08/07/2019	22/08/2019	39	1071
10.1 Statement

RESULT

Book1
	A	B	C	D
1	Statement	Po #	Invoice #	Invoice Date
2	Statement 9-30	4280-01	inv33002
3	Statement 9-30	4276-01	inv33003
4	10.1 Statement	4280-01b	inv33002-10
5	10.1 Statement	4276-01b	inv33003-10
Consolidate

-----------------------------------------------------------------------------------------------------
Test with the following code:

Sub finding_headers()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh As Worksheet
  Dim i As Long, j As Long, k As Long, lr As Long, lr2 As Long, r As Long, n As Long
  Dim dic As Object, f As Range, ky As Variant, una As Boolean
 
  Set sh1 = Sheets("Headers")
  Set sh2 = Sheets("Consolidate")
  Set dic = CreateObject("scripting.dictionary")
 
  For Each sh In Sheets
    Select Case sh.Name
        'Write here the sheets that should not be consolidated
      Case sh1.Name, sh2.Name, "Working", "Report", "Etc"
      
      Case Else
        
        For j = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
          dic(j + 1) = Empty
          For i = 2 To sh1.Cells(Rows.Count, j).End(xlUp).Row
            Set f = sh.Range("A1", sh.Cells(10, Columns.Count)).Find(sh1.Cells(i, j), , xlValues, xlWhole)
            If Not f Is Nothing Then
              dic(j + 1) = f.Column
              r = f.Row
              Exit For
            End If
          Next
        Next
        
        lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
        lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1
        una = False
        For Each ky In dic.keys
          If una = False Then
            n = sh.Range(sh.Cells(r + 1, dic(ky)), sh.Cells(lr, dic(ky))).SpecialCells(xlCellTypeConstants).Rows.Count
            una = True
            sh2.Range("A" & lr2).Resize(n, 1).Value = sh.Name
          End If
          If dic(ky) <> Empty Then
            sh.Range(sh.Cells(r + 1, dic(ky)), sh.Cells(lr, dic(ky))).SpecialCells(xlCellTypeConstants).Copy sh2.Cells(lr2, ky)
          End If
        Next
    End Select
  Next
  MsgBox "End "
End Sub

 

[nikk]

Hi Dante Amor,

It worked to somewhere and then it stopped and gave me the error below on the line below it:

It stopped on the Invoice amount on one of the statements:

I completed the Headers tab with all the headers from the current statements - the first row is the first row in the consolidated statement in the same order:

I circled the header where the macro stopped. I also circled the column that is in 100% of the statements. The first statement that was copied over to the Consolidated tab was cut off on the first cell the PO# (column A on the Headers tab above) was missing.

 

[DanteAmor]

Apparently the mistake is because you have merged cells on one sheet and try to paste on another sheet without merged cells.
merged cells is a problem, if possible you should remove the merged cells.

In the "Headers" sheet, you don't need to repeat the headings, you can put unique ones.

Try again and tell me. If you have any other errors, I think it will be necessary to review your data.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.