Hi all,
I am currently compiling my data into a separate sheet. My data contains 11 different names with varying number of rows of data of the projects they have worked on over the years. After filtering out their data into another sheet, i have 9 columns of formula that I want to display towards the right of the filtered data.
After typing in the formulas that I have, I autofill it downwards and this is where im facing the problem. During recording, the person that I autofilled the 9 working columns only had 8 rows of data, but the next person has 9 with a few of them with 13 rows of data. When running my macro again, it only autofills by 8 instead of the number of rows on the left of the working column which is the filtered data.
My current formula looks like this:
I am currently compiling my data into a separate sheet. My data contains 11 different names with varying number of rows of data of the projects they have worked on over the years. After filtering out their data into another sheet, i have 9 columns of formula that I want to display towards the right of the filtered data.
After typing in the formulas that I have, I autofill it downwards and this is where im facing the problem. During recording, the person that I autofilled the 9 working columns only had 8 rows of data, but the next person has 9 with a few of them with 13 rows of data. When running my macro again, it only autofills by 8 instead of the number of rows on the left of the working column which is the filtered data.
My current formula looks like this:
VBA Code:
Sub OneSetDataExtraction()
'
' OneSetDataExtraction Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Interviewer List").Select
Selection.Copy
Sheets("test").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = _
"=FILTER(DB_Formula!R4C2:R3000C29,DB_Formula!R4C7:R3000C7 = test!R[-2]C)"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Select
Application.CutCopyMode = False
Selection.End(xlToRight).Select
ActiveCell.Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 28).Range("A1").Select
ActiveCell.Formula2R1C1 = _
"=IF(SUMPRODUCT(--(R3C26:R10C28<>""""))=0,"""",""Y"")"
ActiveCell.Select
Selection.AutoFill Destination:=Range("A1:A" & Range("X" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(DB_Formula!R4C7:R3000C7,R1C1,DB_Formula!R4C18:R3000C18,"">0"")"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(DB_Formula!R4C7:R3000C7,R1C1,DB_Formula!R4C20:R3000C20,"">0"")"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=MAXIFS(DB_Formula!R4C20:R3000C20,DB_Formula!R4C7:R3000C7,R1C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=INDEX(R3C18:R10C18,MATCH(R3C32,R3C19:R10C19,0))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=INDEX(R3C22:R10C22,MATCH(R3C32,R3C19:R10C19,0))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=COUNTA(R3C4:R10C4)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C4:R10C4,MATCH(MAX(R3C1:R10C1),R3C1:R10C1,0))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=INDEX(R3C5:R10C5,MATCH(R3C36,R3C4:R10C4,0))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8")
ActiveCell.Range("A1:A8").Select
ActiveCell.Offset(0, -36).Range("A1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveCell.Range("A1:AK1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("PastRecords").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("test").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Cells.Select
Selection.ClearContents
Selection.ClearContents
ActiveCell.Offset(3, 0).Range("A1:AK23").Select
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
ActiveCell.Offset(-3, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Cells.Select
ActiveCell.Offset(4, 0).Range("A1").Activate
Selection.ClearContents
ActiveCell.Offset(-4, 0).Range("A1").Select
Sheets("Interviewer List").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("test").Select
ActiveCell.Cells.Select
Selection.ClearContents
ActiveCell.Select
Sheets("Interviewer List").Select
Sheets("PastRecords").Select
End Sub