Run the code below it should do. A formula using vlookup+index+match should
be best provided the name of your column are the same on both sheet.
As you've set a vba button, I assume you'll be able to link this cod to
your button.
Dim wb As Workbook
Dim wsA As Worksheet
Dim wsB As Worksheet
Dim i As Long, j As Long
Sub DataMove()
Set wb = ThisWorkbook
Set wsA = wb.Worksheets("PROGRESS TRACKING-DATA")
Set wsB = wb.Worksheets("REPORT-INDEX")
Dim Tabkey, TabFin
Dim targ As String
Dim cnt As Long
With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
End With
targ = wsB.Cells(1, 9).Value
Tabkey = FindMatchingRows(targ) 'get row numbers that match
TabFin = FindData(Tabkey) 'Returns the data
'''
'Append the data to the first free place on report-index starting from
first column
'
'''
cnt = wsB.Cells(Rows.Count, 1).End(xlUp).Row
cnt = cnt + 1
For i = 0 To UBound(TabFin)
For j = 0 To 7
wsB.Cells(cnt + i, j + 1).Value = TabFin(i, j)
Next j
Next i
With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With
Set wb = Nothing
Set wsA = Nothing
Set wsB = Nothing
End Sub
Function FindMatchingRows(ftarg As String)
Dim FKeys() As Long
Dim TkLim As Long
Set wsA = wb.Worksheets("PROGRESS TRACKING-DATA")
TkLim = wsA.Cells(Rows.Count, 9).End(xlUp).Row
j = 0: ReDim FKeys(j)
For i = 2 To TkLim
If wsA.Cells(i, 16).Value = ftarg Then
FKeys(j) = wsA.Cells(i, 9).Row
j = j + 1
ReDim Preserve FKeys(j)
End If
Next i
j = j - 1
ReDim Preserve FKeys(j)
FindMatchingRows = FKeys
End Function
Function FindData(Tabkey)
Set wsA = wb.Worksheets("PROGRESS TRACKING-DATA")
Dim FData() As String
Dim PCol() As Long
ReDim jCol(7)
jCol(0) = 6
jCol(1) = 7
jCol(2) = 8
jCol(3) = 5
jCol(4) = 9
jCol(5) = 12
jCol(6) = 13
jCol(7) = 15
ReDim FData(UBound(Tabkey), 7)
For i = 0 To UBound(jCol)
For j = 0 To UBound(Tabkey)
FData(j, i) = wsA.Cells(Tabkey(j), jCol(i))
Next j
Next i
FindData = FData
End Function
Pascal Baro
multiskills.elementfx.com <http://www.multiskills.elementfx.com>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
