Copy range from one sheet to another skipping rows

[henrybrent1974]

I'm trying to copy C4 thru C19 from sheet (Sign Up) to sheet (16 Man Bracket) column AC. Need it to start on row 8 then skip a row 3 times then skip 3 rows and repeat until all 16 have been pasted per example below. I used macro recorder to do this but after reading on this forum that isn't the most efficient way of doing this. This will be put in my code to take place of the copy and pasting of each cell one by one.The names are not important just used for example. Thanks in advance for any help.

AC

Ted

Jen

Dan

Ben

Ted

Jen

Dan

Ben

Ted

Jen

Dan

Ben

Ted

Jen

Dan

Ben

<tbody>
</tbody>

 

[JackDanIce]

Try:

Sub MoveDataNoBlanks()

    Dim arr()   As Variant
        
    Application.ScreenUpdating = False
        
    With Sheets("Sign Up")
        arr = .Cells(4, 3).Resize(.Cells(.Rows.count, 3).End(xlUp).row - 3).Value
    End With
        
    With Sheets("16 Man Bracket").Cells(.Rows.count, 29).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2))
        .Value = arr
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub

 

[JackDanIce]

Typo in above, try:

Sub MoveDataNoBlanks()

    Dim arr()   As Variant
        
    Application.ScreenUpdating = False
        
    With Sheets("Sign Up")
        arr = .Cells(4, 3).Resize(.Cells(.Rows.count, 3).End(xlUp).row - 3).Value
    End With
        
    With Sheets("16 Man Bracket").Cells(Rows.count, 29).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2))
        .Value = arr
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub

 

[henrybrent1974]

With Sheets("16 Man Bracket").Cells(.Rows.count, 29).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2))

I'm receiving a compile error invalid or unqualified reference where it says .Rows

 

[JackDanIce]

Try the code in post #3 , it had an extra . before Rows.Count that needed removing

 

[henrybrent1974]

code runs and pasting in other sheet but it's not skipping like i need. In column AC it should start on row 8 paste name then skip row paste name then skip row paste name then skip row paste name then skip 3 rows paste name then skip row paste name etc.

 

[JackDanIce]

Try:

Sub MoveDataNoBlanks()

    Dim arr()   As Variant
    Dim x       As Long
    Dim r       As Long
    Application.ScreenUpdating = False
        
    With Sheets("Sign Up")
        arr = .Cells(4, 3).Resize(.Cells(.Rows.count, 3).End(xlUp).row - 3, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 2) = 2
        If x Mod 4 = 0 Then arr(x, 2) = 4
    Next x
    
    r = 8
    With Sheets("16 Man Bracket")
        For x = LBound(arr, 1) To UBound(arr, 1)
            .Cells(r, 29).Value = arr(x, 1)
            r = r + arr(x, 2)
        Next x
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub

 

[henrybrent1974]

Works great!!!! Thanks

 

[JackDanIce]

You're welcome