vba macro that copy paste data off many tabs into a single one

[marianocaifanjaguar]

hello. i would appreciate very much any of you fine folks' assistance
i have attached sample
i need a macro that copy and pastes data off many tabs into a single consolidatin tab which will be the 1st tab, from left-to-right. the basis to determine what data to copy/paste will be based on header name
the problem is that the order of the headers (columns) in each of the source data tabs can and will be different. also the extend or qty of records (rows) can and will be different on each tab. macro code should be flexible to account as many columns to the right or rows to the bottom
The consolidating tab (1st one) will have manual maintenance by me so that all preexisting headers (and future ones) already exist.... before running the macro. The button of macro can be located in the first tab.
The info that gets copy and pasted ...after paste function needs to be in the same format (currency, date, general, text, etc) depending on the source data tabs contents.
On the 1st run of the macro you kindly submit to me, i believe aprox 16 to 24 tabs of info need to be copied and pasted in the consolidated tab. The order of pasting is as the order of the tabs from left to right (excluding 1st tab)
so the paste function need not to overwrite data of previous or next copy
After the 1st run of data, if information already exists in 1st tab consolidating, then macro when ran "must be aware" that there is preexisting info...so whatever addtl tabs have been manually added or included in file...those would be subject to the copy-paste functions and not overwrite info preexisting in 1st tab
i apologize for my english. is not good. hope any of you superb excel geniuses understand it, and what i wanna convey.
in advance..your help will be vastly appreciated as always
feel free to ask any questions.
thanks again. i await feedback if possible today or tomorrow morning. im sorry to rush. thanks once again! be safe.

 

[p45cal]

The attached should get you started. Button at cell F1.
It will not overwrite existing data in the 'deposit here' sheet, but it will copy stuff again, so if you run the macro twice you will have duplicate rows in 'deposit here'. Either start with an empty 'deposit here' sheet (except for the headers) or use Excel's remove duplicates.
I've added one more column to the 'deposit here' sheet, a column headed 'Source Sheet'. You can remove it if it's not needed along with the line of code that puts it there.
This is the code:

Sub blah()
On Error GoTo ExitNow
Application.ScreenUpdating = False
Set DestnSht = Sheets("deposit here")
DestnRow = DestnSht.Cells.Find("*", DestnSht.Range("A1"), xlFormulas, xlWhole, , xlPrevious, , , False).Row + 1
SourceShtDestnColumn = Application.WorksheetFunction.Match("Source Sheet", DestnSht.Rows(1), 0)
For Each sht In ThisWorkbook.Sheets
  If sht.Name <> "deposit here" Then
    Set DataRng = Range(sht.Range("a1"), sht.Cells.Find("*", sht.Range("A1"), xlFormulas, xlWhole, , xlPrevious, , , False))
    DataBodyRngRowCount = DataRng.Rows.Count - 1
    If DataBodyRngRowCount > 0 Then
      For Each colm In Intersect(DataRng, DataRng.Offset(1)).Columns
        If Application.CountBlank(colm) <> colm.Cells.Count Then
          'Application.Goto colm
          ColmHeader = colm.Cells(1).Offset(-1).Value
          DestnColumn = Application.Match(ColmHeader, DestnSht.Rows(1), 0)
          'Application.Goto DestnSht.Cells(DestnRow, DestnColumn)
          colm.Copy DestnSht.Cells(DestnRow, DestnColumn)
        End If
      Next colm
      DestnSht.Cells(DestnRow, SourceShtDestnColumn).Resize(DataBodyRngRowCount) = sht.Name
    End If
  End If
  DestnRow = DestnRow + DataBodyRngRowCount
Next sht
ExitNow:
Application.ScreenUpdating = True
End Sub

 

[marianocaifanjaguar]

you are awesome. thanks for helping a complete stranger. one Q: can i change/swap the headers to a different location or column? like id like be able to read the source tab header under column A and that means inserting column and moving the other headers to the right. the code wont be affected if i decide to relocate headers, am i correct? thanks again. you are a good fella (lady, in case of a she-excel genius!)

 

[p45cal]

Q: can i change/swap the headers to a different location or column? like id like be able to read the source tab header under column A and that means inserting column and moving the other headers to the right. the code wont be affected if i decide to relocate headers, am i correct?

Yes, and Yes. Same row, any column.

 

[marianocaifanjaguar]

will give i a try then. gracias de nuevo.