Continuous Copy and paste

[john tempest]

This code is attached to a button and works ok the first time the button is pushed. I would like the button on the second time to paste into C4 and then onwards to C33.

 ActiveSheet.Unprotect
    Range("P4:R4").Select
    Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Thank you John

 

[Joe4]

So, are you saying that you want to copy P4:R4 first to C3, then the next time to C4, and then to C5, etc?
Is there anything in cell C2 to start (like a header or something)?

 

[Joe4]

If my assumptions are correct (and there are no populated cells below C33), then this code should do what you want:

    Dim nr As Long
   
'   Find next row to put data in in column C
    nr = Cells(Rows.Count, "C").End(xlUp).Row + 1
   
'   See if next row is row 33 or less
    If nr <= 33 Then
        ActiveSheet.Unprotect
        Range("P4:R4").Copy
        Range("C" & nr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End If

 

[john tempest]

This is to record my blood pressure every day for a month. P4:P5 are the average of three blood pressure readings.
these are then to be pasted into C3:C5. headers are in C2:C5. Systolic Diastolic Heart rate.

 

[john tempest]

Nothing happens at all with that code. Is it because of the headers
Regards John

 

[Joe4]

It is kind of hard to know what the issue is without showing exactly how your sheet is structured and what your data looks like (since you didn't post those details, I had to make assumptions).
Can you post two images, a "before" image of what your data looks like to start, and an "after" image of your desired results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[john tempest]

I have posted a link. First time I have done this so I don't know if it will work

John's Body test.xlsm

 

[Joe4]

OK, I cannot download files from my current location (corporate security policy), but I can try to look at it later tonight when I am in front of my own computer.

 

[Joe4]

OK, I think I see the issue. You have totals line at the bottom that are messing up the last row calculation.
Try this variation instead:

    Dim nr As Long
   
'   Find next row to put data in in column C
    nr = Range("C1").End(xlDown).Row + 1
   
'   See if next row is row 33 or less
    If nr <= 33 Then
        ActiveSheet.Unprotect
        Range("P4:R4").Copy
        Range("C" & nr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End If

 

[john tempest]

Thank you that works perfectly
Regards John