Colsoldation of multiple files to one master

[kiki_xy]

Dear all,

I am trying to consolidate multiple excel files with different sheets to one file that was initial file that was split into several files.

So I have multiple files that contains part of the file and I would like to collect the feedback from that in the Initial master file.

My logic open each file look for the value "1234" in column A which is unique, move 29 rows to the right where is the input, copy, then move to masterfile, set the same sheetname as in the previous file, look for value "1234", move 29 rows to the right and then paste the value.

Loop this trough all the used cells in column A, then move to the next sheet. When all sheets are finished move to the next file.

I tried to create something with one file To copy from Input.xlsm to Cons.xlsm, I got stuck and doesn't work as desired, all the help is appreciated.



Sub Consolidate()
'
'
Dim shtname As String
shtname = ActiveSheet.Name

Dim WS_Count As Integer
'' Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
'' WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
'' For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.

Windows("Input.xlsm").Activate
Worksheets(shtname).Activate


'For Each cell In Range("A2:A30")
For Each cell In Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)

'Search Unique number in Input

Columns("A:A").Select
Selection.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 29).Select

'Search Unique number in Consolidation

Windows("Cons.xlsm").Activate
Worksheets(shtname).Activate
Columns("A:A").Select
Selection.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 29).Select

'Copy value from Input

Windows("Input.xlsm").Activate
cell.Select
ActiveCell.Offset(0, 29).Select
Application.CutCopyMode = False
Selection.Copy

' Paste in Consolidation

Windows("Cons.xlsm").Activate
ActiveSheet.Paste

Next

'' Next I


End Sub

Sub RunMacroOnAllSheetsToRight()
For i = ActiveSheet.Index To Sheets.Count
Call MyFunction(i)
Next i
End Sub

Function MyFunction(i)
'Code goes here
Dim SAP As Integer
Dim shtname As String
shtname = ActiveSheet.Name


Windows("Input.xlsm").Activate
Worksheets(shtname).Activate


'For Each cell In Range("A2:A30")
For Each cell In Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)

'Search SAP ID in Input

Columns("A:A").Select
Selection.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 29).Select

'Search in Consolidation

Windows("Cons.xlsm").Activate
Worksheets(shtname).Activate
Columns("A:A").Select
Selection.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 29).Select

'Copy value from Input

Windows("Input.xlsm").Activate
cell.Select
ActiveCell.Offset(0, 29).Select
Application.CutCopyMode = False
Selection.Copy

' Paste in Consolidation

Windows("Cons.xlsm").Activate
ActiveSheet.Paste

Next


End Function