Sub Macro()
Dim Workingfile As Workbook
Dim PSdata As Workbook
Dim PDMT As Workbook
Dim Commodityfile As Workbook
Dim Segmentfile As Workbook
Dim Marketfile As Workbook
Dim Lrow As Long
Set Workingfile = ActiveWorkbook
'fetching PSdata into workingfile
MsgBox ("Choose PSdata")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set PSdata = Workbooks(FIleNm)
PSdata.Activate
Lrow = Range("A4").End(xlDown).Row
Range("A4", "L" & Lrow).SpecialCells(xlCellTypeVisible).Copy
Workingfile.Activate
Sheets("Sheet1").Activate
Range("A1").PasteSpecial xlPasteFormulasAndNumberFormats
PSdata.Close
Workingfile.Activate
Columns("D:D").Insert Shift:=xlToRight
Columns("E:E").Insert Shift:=xlToRight
Columns("K:K").Insert Shift:=xlToRight
Columns("L:L").Insert Shift:=xlToRight
Columns("P:p").Insert Shift:=xlToRight
MsgBox ("Choose Commodityfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Commodityfile = Workbooks(FIleNm)
Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("C2").End(xlDown).Row
'Range("D2", "D" & Lrow).Formula = "= Vlookup(C2,'[" & FileNm & "]Sheet1'!$A:$C,3,0)"
Range("D2", "D" & Lrow).Formula = "=C2&"" - ""&Vlookup(C2,'[" & FIleNm & "]Sheet1'!$A:$C,3,0)"
Range("E2", "E" & Lrow).Formula = "= Vlookup(C2,'[" & FIleNm & "]Sheet1'!$A:$D,4,0)"
Commodityfile.Close
MsgBox ("Choose Segmentfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Segmentfile = Workbooks(FIleNm)
Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row
Range("K2", "K" & Lrow).Formula = "= Vlookup(J2,'[" & FIleNm & "]Sheet1'!$B:$M,12,0)"
Segmentfile.Close
MsgBox ("Choose Marketfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Marketfile = Workbooks(FIleNm)
Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row
Range("L2", "L" & Lrow).Formula = "= Vlookup(K2,'[" & FIleNm & "]Sheet1'!$A:$B,2,0)"
Marketfile.Close
MsgBox ("Choose PDMT")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set PDMT = Workbooks(FIleNm)
Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row
Range("P2", "P" & Lrow).Formula = "= Vlookup(O2,'[" & FIleNm & "]Original data'!$O:$P,2,0)"
PDMT.Activate
Sheets("Original Data").Select
Lrow = Range("A4").End(xlDown).Row
Range("A4", "Q" & Lrow).ClearContents
Workingfile.Activate
Sheets("Sheet1").Activate
Range("A2", "Q" & Lrow).Copy
PDMT.Activate
Sheets("Original data").Activate
Range("A4").PasteSpecial xlPasteValues
End Sub