thomsonreuters
New Member
- Joined
- Dec 9, 2017
- Messages
- 26
i have many files with different criteria to copy and paste into current workbook.
every time want to activate file name mention the Sheet 2 columnA (workbook name) and columnB (sheet name) and filter based on value present in the column C, copy and paste to Sheet 3 . Filter order should be same(creteria value is scuffled in source data). i have tries below codes but not working...am not good in VBA....just referring online examples and tried...
PLease could you any one help on this...
Sub looptest()
Dim LastRow As Long, LastrowA As Long
LastRow = ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow
With LastrowA = ThisWorkbook.Worksheets("sheet3").Range("A5").CurrentRegion.Rows.Count
Workbooks("Cells(i, 2).Value" & .xlsx).Sheets("& Cells(i, 3).Value").Activate
Selection.AutoFilter
Range("A2:AF" & LastRow).Select
Selection.AutoFilter field:=32, Criteria1:="Cells(i, 4).Value", Operator:=xlFilterValues
Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("Sheet3").Range("A" & LastrowA + 1).PasteSpecial xlPasteFormulas
ThisWorkbook.Worksheets("Sheet3").Range("A" & LastrowA + 1).PasteSpecial xlPasteFormats
End With
Next
End Sub
every time want to activate file name mention the Sheet 2 columnA (workbook name) and columnB (sheet name) and filter based on value present in the column C, copy and paste to Sheet 3 . Filter order should be same(creteria value is scuffled in source data). i have tries below codes but not working...am not good in VBA....just referring online examples and tried...
PLease could you any one help on this...
Sub looptest()
Dim LastRow As Long, LastrowA As Long
LastRow = ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow
With LastrowA = ThisWorkbook.Worksheets("sheet3").Range("A5").CurrentRegion.Rows.Count
Workbooks("Cells(i, 2).Value" & .xlsx).Sheets("& Cells(i, 3).Value").Activate
Selection.AutoFilter
Range("A2:AF" & LastRow).Select
Selection.AutoFilter field:=32, Criteria1:="Cells(i, 4).Value", Operator:=xlFilterValues
Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("Sheet3").Range("A" & LastrowA + 1).PasteSpecial xlPasteFormulas
ThisWorkbook.Worksheets("Sheet3").Range("A" & LastrowA + 1).PasteSpecial xlPasteFormats
End With
Next
End Sub
File Name - Column A | Sheet - Column B | Filter criteria - Column C |
PB3-ALL-20_SAVIG.xlsx | SAVIG QC Checked | WO2020060735 |
PB3-ALL-20_SAVIG.xlsx | SAVIG QC Checked | US2020095665 |
PB3-ALL-20_SAVIG.xlsx | SAVIG QC Checked | US2020094354 |
PB3-ALL-20_SAVIG.xlsx | SAVIG QC Checked | US2020098496 |
PB3-ALL-20_KPUSH.xlsx | KPUSH QC Checked | US2020098497 |
PB3-ALL-20_KPUSH.xlsx | KPUSH QC Checked | US2020091514 |