Filter the specific column on basis of criteria and populate columns in new excel template

[AmitSingh]

Hi All,

Need help to extract the 8 column from the input file name "TestProduct_DELOITTE OI-421564 RMTv15_checked01.xlsm" and past it to next workbook sheet by the name starting with "Product_TestProduct_DELOITTE OI-421564 RMTv15_checked01.xlsb". Filter to be applied in LOB column (I13) with "Product Line". After filter only, below columns to be pasted from one sheet to another workbook.

Name of column to be extracted from Input file are below:-
Salesforce ID from Cell I4
Start Date from Column T13
End Date from Column V13
LOB from Column I13
Resource Source from Column O13
Roll Title from Column M13
Region/Country from Column L13
Master Roll Code from Column X13

I have attached the Input file and output file template(Product_TestProduct_DELOITTE OI-421564 RMTv15_checked01) where i need the result. Please help me as it is little urgent.

Thanks in advance.

 

[p45cal]

This will only get you started because I don't know whether you want the xlsb file created or to find it somewhere if it already exists.
Run this snippet of code in your xlsm file which you attached here. It creates a new sheet. See also comment in the code.

Sub blah()
StrDestn = "B3" 'adjust this; it's the top left cell of the results table in the new sheet.
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
With NewSht.Range(StrDestn)
  .Resize(, 10).Value = Array("Salesforce Opportunity ID:", "Start Date" & Chr(10) & "(dd-mmm-yy)      ", "End Date", "LOB", "Resource Source", "Role Title", "REGION/Country", "Master Role Code", , "LOB")
  .Offset(1, 9) = "Product Line"
  Intersect(Sheets("Input").Range("B13").CurrentRegion, Sheets("Input").Rows("13:1000")).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=NewSht.Range(StrDestn).Offset(, 9).Resize(2), CopyToRange:=NewSht.Range(StrDestn).Offset(, 1).Resize(, 7), Unique:=False
  .Offset(, 9).Resize(2).Clear
  .CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks).Value = Sheets("Input").Range("I4").Value
  .CurrentRegion.WrapText = False
  .Resize(, 8).EntireColumn.AutoFit
End With
End Sub

 

[AmitSingh]

Thanks for your reply and it is working fine.