There are two pages in Excel that you want to open. It is desired to make a Vlookup (Vertical Vertical) from one column between the pages to the other column. Control1, Control2, and Control3 are written in the header of the relevant columns. At the end of the whole Vlookup process, it is saved as another excel file. To perform Vlookup operations, there should be no filtered columns in excel.
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
objXLApp.DisplayAlerts = False
Set objXLWb = objXLApp.Workbooks.Open("C:\Robusta\Excel Folder\Perakende_072021_VBS.xlsx")
Set oSheet = objXLWb.Worksheets(2)
Set oSheet2 = objXLWb.Worksheets(1)
oSheet2.Cells(1,2).Value="Kontrol1"
oSheet.Cells(1,2).Value="Kontrol2"
oSheet.Cells(1,10).Value="Kontrol3"
Dim rowCount
rowCount = oSheet.Range("A1").CurrentRegion.Rows.Count
Dim rowCount2
rowCount2 = oSheet2.Range("A1").CurrentRegion.Rows.Count
oSheet.Range("J2").Formula ="=VLOOKUP(I2,'Noktalar (2)'!I:I,1,0)"
oSheet.Range("J2:J" & rowCount).FillDown
oSheet.Range("B2").Formula ="=VLOOKUP(A2,'Noktalar (2)'!C:C,1,0)"
oSheet.Range("B2:B" & rowCount).FillDown
oSheet2.Range("B2").Formula ="=VLOOKUP(C2,'Noktalar'!A:A,1,0)"
oSheet2.Range("B2:B" & rowCount2).FillDown
oSheet2.Cells(1,2).Value="Kontrol1"
oSheet.Cells(1,2).Value="Kontrol2"
oSheet.Cells(1,10).Value="Kontrol3"
objXLWb.SaveAs "C:\Robusta\Output\Perakende_072021_Son.xlsx", 51
objXLWb.Close
objXLApp.Quit