0

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:

  1. https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ [from 2016]

  2. 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!

Glenn98
  • 1
  • 1
  • You can not use space in variable name. So, change `Selection Status Col` to `SelectionStatusCol`. – Harun24hr Jun 06 '21 at 09:24
  • Thanks for the advice. @Harun24HR. I have just adjusted this. I have new problem and have updated my current problem: **Run-time error "9". Subscript out of range**. What do I do now? – Glenn98 Jun 06 '21 at 09:35
  • Why not just use autoFilter and paste all the visible cells in one step; or use the Advanced Filter which has a paste option included. – Ron Rosenfeld Jun 06 '21 at 10:41
  • Great idea. I feel dumbfounded that it's such an easy step although I would need to still but not effortfully copy & paste the resulting filters into another sheet. Many thanks! However I would still let someone to answer the question for knowledge's sake and other future use :) – Glenn98 Jun 07 '21 at 11:06

0 Answers0