Variable Copy paste in VBA

[saibb]

Hi,
I need help with having to copy four fields individually and then pasted in another field. Although this sounds easy, the problem I run into is that each time, the field to paste on chnages by 8 lines. Example, I need a macro that will copy from field C4 then it will paste it in field C13. If I want to do the same again, it will copy again from C4 but this time it will paste in C21. Then again it will past in C29. Always leaving the previoue paste intact.

You guys have always benn great in helping... so thanks in advance.

B

 

[DonkeyOte]

Is this what you mean -- ie one off repetitive task as opposed to loop ?

Below is set to fire from activecell -- if you want to fire for given ranges you may need to change - meant to be an indicative solution only.

Sub copy_offset()
Dim i As Integer: i = 1
Dim o As Integer: o = 8
Do Until IsEmpty(ActiveCell.Offset(i * o, 0))
    i = i + 1
    If (ActiveCell.Row + (i * o)) > 65536 Then
        MsgBox "No More Blanks Available Matey", vbCritical, "No Can Do"
    End If
Loop
Range(ActiveCell.Address, ActiveCell.Offset(0, 3)).Copy Cells(ActiveCell.Row + (i * o), 3)
End Sub

 

[saibb]

Dude I have no idea what you just asked me or what you wrote. I am a dentist and not a VBA writer. I record macros and use them. If you can write a VBA to do exactly the cell transfers I asked then I can incorporate it into the macro I have recorded. Otherwise, you might as well write or seapk chinese to me. I am not criticizing you, and I do appreciate you trying to help. Is there a simpler way of doing it? or at least something I can try to understand?

B

 

[Peter_SSs]

Try this code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_C4()<br>    <SPAN style="color:#00007F">Dim</SPAN> DestinationCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Range("C13").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> DestinationCell = Range("C13")<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> DestinationCell = Range("C" & Rows.Count).End(xlUp).Offset(8)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Range("C4").Copy Destination:=DestinationCell<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[saibb]

OK I think I might be able to work with this one. Thanks for that.
B

 

[DonkeyOte]

Saibb, we offer solutions -- if you're not prepared to learn on your part or you only want solutions you immediately understand without any investigation on your part you probably should not be requesting help in the first instance ?

Also it does help if the examples you give are consistent - your examples were not I'm afraid (4 to 13 (9), 13 to 21 (8), 21 to 29 (8)) etc...

Thanks,