VBA vlookup different workbook and specific sheet

[ce1990]

Hi everyone,

I have the below code which works to look up a specific work book but I also want to tell it to go to a specific worksheet and then it pastes into a different workbook. Can someone point out where in the code I need to specify the sheet and what I need to type?

Sub filetest()
Dim FilenamePrefix As String
FilenamePrefix = VBA.Format(Date, "mmm")

Dim CSVFilename As String
CSVFilename = FilenamePrefix & "2020 Forecast Tracker - Macro Tracker"

'
Range("A1").Select
ActiveCell.Formula2R1C1 = _
"=IF('" & CSVFilename & "'!RC1:R[250]C1=""BFProg"",'" & CSVFilename & "'!RC[1]:R[249]C[1], 0)"
Columns("A:A").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("C1").Select
ActiveSheet.Paste
Range("D1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
Range("F1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = _
"=IF('" & CSVFilename & "'!RC1:R[249]C1=""BFProg"",'" & CSVFilename & "'!RC[3]:R[249]C[3], 0)"
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

 

[decadence]

How many workbooks do you have? what are the sheet names you want to get the data from and paste the data to?