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
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