Avoid each third cell while copying or pasting a range.

[shaju]

I have a range to copy and paste.
The range is Sheet2.Range("BA3:EF3"), but the catch is that each third cell should be avoided....either while copying OR when it is pasted to another location.
First part is like:

Sheet2.Range("BA3:BB3,BD3:BE3,BG3:BH3,BJ3:BK3,BM3:BN3,BP3:BQ3,BS3:BT3,BV3:BW3,BY3:BZ3,CB3:CC3,CE3:CF3,CH3:CI3,CK3:CL3,CN3:CO3,CQ3:CR3,CT3:CU3,CW3:CX3,CZ3:DA3,DC3:DD3,DF3:DG3,DI3:DJ3,DL3:DM3,DO3:DP3,DR3:DS3,DU3:DV3,DX3:DY3,EA3:EB3,ED3:EE3").Select

Because the second location where it is to be pasted has formulae in all third cells.
Second location is Sheet6.Range("B" & i ":CJ" & i) where i is a variable


I am confused mainly with second part ie, pasting it. But, any help with the first part also welcome.

Any ideas, insights...etc most welcome

 

[Snakehips]

@shaju I'm not sure that your stated source and destination ranges match??

However, rather than select, copy, paste, does something like the below approach help?

For c = 0 To 81 Step 3
 Sheets("Sheet6").Range("B" & i & ":C" & i).Offset(0, c).Value = Sheets("Sheet2").Range("BA3:BB3").Offset(0, c).Value
Next c

 

[Automatrix]

Power Query solution: Remove Alternate Rows action.

 

[shaju]

@shaju I'm not sure that your stated source and destination ranges match??

However, rather than select, copy, paste, does something like the below approach help?

For c = 0 To 81 Step 3
 Sheets("Sheet6").Range("B" & i & ":C" & i).Offset(0, c).Value = Sheets("Sheet2").Range("BA3:BB3").Offset(0, c).Value
Next c

Thanks A Lot Snakehips. It worked like a charm.
My VB Knowledge is what I have gained from this site, through learned persons like yourself.
Although I have used STEP in some other codes, didn't knew it has this much power.
Regarding "source and destination ranges"....the same was corrected because of yourself....Thanks for that too.

 

[shaju]

Power Query solution: Remove Alternate Rows action.

That is quite a new suggestion....for me, about which I have not much idea. But shall surely give a try with that too.
Thanks for your input.

 

[HongRu]

try.

Sub Each_third_cell()
    For i = 1 To 10  'I suppose you have 10 rows data
        myData = Sheet2.Range("BA3:EF3").Offset(i - 1, 0) 'suppose your first row of data is BA3
        myData_Num = UBound(myData, 2)
        For j = 1 To myData_Num
            c = j Mod 3
            If c > 0 Then
                Sheet6.Range("B4").Offset(i - 1, j - 1) = myData(1, j) 'suppose your first row to fill is B4
            End If
        Next j
    Next i
End Sub

 

[shaju]

try.

Sub Each_third_cell()
    For i = 1 To 10  'I suppose you have 10 rows data
        myData = Sheet2.Range("BA3:EF3").Offset(i - 1, 0) 'suppose your first row of data is BA3
        myData_Num = UBound(myData, 2)
        For j = 1 To myData_Num
            c = j Mod 3
            If c > 0 Then
                Sheet6.Range("B4").Offset(i - 1, j - 1) = myData(1, j) 'suppose your first row to fill is B4
            End If
        Next j
    Next i
End Sub

Thank you HongRu for your valuable suggestion.
It is the diversity in approaches that makes this site a real knowledge base.
I opted for the code suggested by Snakehips, as it is shorter. But I thank you for your effort and time you used for me.

 

[bebo021999]

Thank you HongRu for your valuable suggestion.
It is the diversity in approaches that makes this site a real knowledge base.
I opted for the code suggested by Snakehips, as it is shorter. But I thank you for your effort and time you used for me.

Happy to hear you get solution.
Anyway, it would be a real pain, if there were thousands of rows need to be updated value... ( for i = 1 to 10000, for eaxample...)

 

[shaju]

Happy to hear you get solution.
Anyway, it would be a real pain, if there were thousands of rows need to be updated value... ( for i = 1 to 10000, for eaxample...)

You said it
The scenario here is...
Sheet2 collects the summary of a days activity in granular mode, and the same is added to a register in Sheet6. But sheet6 has running formula in each third cell.
Anyway, thanks to the forum members for their helping hand, for solving this problem.

 

[Peter_SSs]

Thanks A Lot Snakehips. It worked like a charm.

The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that answered your question instead of your feedback message as it will help future readers. No further action is required for this thread.