i found solution with next code
Sub MasterMine()
Dim Master As Worksheet
Dim LR1 As Long, LR2 As Long, LC1 As Long, LC2 As Long
Dim ws As Worksheet
Dim Found As Range
Dim i As Long
Dim Arr() As Variant
Dim pas As Worksheet
Dim headers As Range
Dim SheetExists As Boolean
'Set Master sheet for consolidation
Set wb = ActiveWorkbook
SheetExists = False
Set pas = ActiveSheet
For Each ws In ActiveWorkbook.Sheets
If ws.Name = "AllSheets" Then
SheetExists = True
End If
Next ws
If SheetExists = False Then
Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = "AllSheets"
Set Master = ActiveWorkbook.Sheets("AllSheets")
pas.Activate
'Get Headers
Set headers = Application.InputBox("Izaberi opseg Header-a", Type:=8)
'Copy Headers into master
headers.Copy Master.Range("A1")
LC1 = Master.Cells(1, Master.Columns.Count).End(xlToLeft).Column
Arr = Application.Transpose(Application.Transpose(Master.Range(Master.Cells(1, 1), Master.Cells(1, LC1)).Value))
ElseIf SheetExists = True Then
Set Master = ActiveWorkbook.Sheets("AllSheets")
pas.Activate
LC1 = Master.Cells(1, Master.Columns.Count).End(xlToLeft).Column
If IsEmpty(Master.Range(Master.Cells(1, 1), Master.Cells(1, LC1)).Value) Then
MsgBox "Postoji Sheet AllSheets, ali nema imena kolona. Unesite nazive kolona!"
End
End If
If LC1 = 1 Then
Arr = Master.Range(Master.Cells(1, 1), Master.Cells(1, LC1)).Value
Else
Arr = Application.Transpose(Application.Transpose(Master.Range(Master.Cells(1, 1), Master.Cells(1, LC1)).Value))
End If
End If
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "AllSheets" Then
For i = LBound(Arr) To UBound(Arr)
LC2 = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set Found = ws.Range(ws.Cells(1, 1), ws.Cells(1, LC2)).Find(Arr(i), LookIn:=xlValues)
If Not Found Is Nothing Then
LR1 = Master.Cells(Master.Rows.Count, i).End(xlUp).Offset(1).Row
LR2 = ws.Cells(ws.Rows.Count, Found.Column).End(xlUp).Row
ws.Range(ws.Cells(2, Found.Column), ws.Cells(LR2, Found.Column)).Copy
Master.Cells(LR1, i).PasteSpecial xlPasteValues
End If
Next i
End If
Next ws
End Sub
but i have one more problem. when i type range A1 i receive error run-time error 13, typemismatch. and when i click on debug i go to line
Arr = Application.Transpose(Application.Transpose(Master.Range(Master.Cells(1, 1), Master.Cells(1, LC1)).Value))
What i do?
Bookmarks