Forum Discussion
Solved
Use VBA to Autofill a Row until the end of the number of data in another row
Hello,
I need some help with the following problem:
The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that ...
Hi Haytham Amairah,
I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from:
Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Selectto:
Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Hi,
I suggest you post this question in a separate post by starting a new conversation in here.
If you didn't find the answer, you may ask this https://stackoverflow.com/questions/tagged/vba instead under [vba] tag.
Regards
Hi @Haytham, I'm trying to do the same as above to autofill multiple columns to varying rows and I have tried a couple of different things I've seen above, but I fear I don't have the excel coding knowledge to know what I'm doing wrong. the following is my macro and I'd like the "E145040" to be variable. I also dont know if I need to change the "$A$915118", "$C$915118", "$E$915118", "$G$915118" and "$AC$145040" as well. Thank you so much in advance! Sub Macro5() ' ' Macro5 Macro ' ' Range("Y1").Select ActiveCell.FormulaR1C1 = "COMP" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-21]=""Closed"",1,IF(RC[-21]=""Cancelled"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.Select ActiveCell.FormulaR1C1 = "PROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-22]=""Closed"",RC[-22]=""Verified"",RC[-22]=""Ready_To_Verify"",RC[-22]=""Cancelled""),1,IF(OR(RC[-22]=""Assigned"",RC[-22]=""Reassigned"",RC[-22]=""Pending""),IF(COUNTIF(RC[-11]:RC[-8],""Yes/Oui"")>0,1,0),0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "PC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-23]=""Reassigned"",RC[-23]=""Assigned"",RC[-23]=""Pending""),IF(COUNTIF(RC[-12]:RC[-9],""Yes/Oui"")>0,1,0),0)" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "VER" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-24]=""Verified"",1,IF(RC[-24]=""Ready_To_Verify"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "UNPROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-25]=""Closed"",RC[-25]=""Verified"",RC[-25]=""Ready_To_Verify"",RC[-25]=""Cancelled""),0,IF(OR(RC[-25]=""Assigned"",RC[-25]=""Pending""),IF(COUNTIF(RC[-14]:RC[-11],""Yes/Oui"")>0,0,1),1))" ActiveCell.Offset(0, -4).Range("A1:E1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:E145040") ActiveCell.Range("A1:E145040").Select Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=25, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("Z2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=26, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("C1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=27, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("E1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=28, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("G1").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False ActiveSheet.Range("$A$1:$A$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("C:C").Select ActiveSheet.Range("$C$1:$C$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("E:E").Select ActiveSheet.Range("$E$1:$E$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("G:G").Select ActiveSheet.Range("$G$1:$G$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=9, Criteria1:= _ "PA Exceptions – Exception PA" Cells.Select Range("J1").Activate Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet3!R1C1:R981068C29", Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _ :=xlPivotTableVersion15 Sheets("Sheet4").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable3").PivotFields("User ID") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("PROC"), "Sum of PROC", xlSum ActiveWindow.SmallScroll Down:=-84 Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets("Sheet1").Select Sheets("Sheet1").Name = "1.0 PA RAW" Sheets("Sheet4").Select Sheets("Sheet4").Name = "2.1 production" Sheets("Sheet3").Select Sheets("Sheet3").Name = "2.0 PA exceptions" Sheets("Sheet2").Select Sheets("Sheet2").Name = "1.1 Daily update" End Sub