Edit: I am running on Excel 2016 (if that information help)
Purpose:
I wanted to copy paste entire row for each participant A: ___
from one worksheet Worksheets("Sheet3")
to another worksheet Worksheets("Sheet4")
. based on condition "Selected"
(Column I)
Edit: I have fixed this part below
I have one error where variable not defined for ParticipantCol
. And I am not sure what other particular coding has issues.
Current Problem
Run-time error "9". Subscript out of range. Please also check if my variables are correctly defined.
I relied on these two links for reference to build the syntax below:
https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ [from 2016]
https://www.youtube.com/watch?v=W4swMZe0TEE [from 2021]
Sub CopyPasteIfSelectedThen2021() Dim SelectionStatusCol As Range Dim SelectionStatus As Range Dim PasteCell As Range Set SelectionStatusCol = Worksheets("Sheet2").Range("I2:I23") For Each SelectionStatus In SelectionStatusCol If Worksheets("Sheet2").Range("I2") = "Selected" Then Set PasteCell = Worksheets("Sheet4").Range("A2") Else Set PasteCell = Worksheets("Sheet4").Range("A2").End(xlDown).Offset(1, 0) End If If SelectionStatus = "Selected" Then SelectionStatus.EntireRow.Copy PasteCell Next SelectionStatus End Sub
I tried 2021 coding concept and it shown error. After adjusted towards 2016 coding method, I still experienced issues. And I am not sure where I could find specific references to specific coding for 2016 Excel version and it has taken me day 2 trying me to resolve it.
Appreciate the help!