Take values from different area of a column with a variable range lenght

[RIC63]

I would like to take some values from specific position of a column and paste it in adiacent columns, the number of values to taken can vary then i'm looking for a macro that take as input ( via an input box or directly through a value placed in a cell ) the desired value to use as lenght of range.


for istance :


es. starting from K3 and going down of 3 cells (up to K5) and starting from K17 and going up of 3 cells (up to K15) start point and directions are fixed ....herebelow a small portion of code about wath i have made using the 'record macro' function but clearly far from giving me the desired result...

Sub Auto1()
'
' Auto1 Macro
'
Range("K3:K5").Select
Application.CutCopyMode = False
Selection.Copy
Range("AP4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K15:K17").Select
Range("K17").Activate
Application.CutCopyMode = False
Selection.Copy
Range("AQ4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I apprieciate any suggestion
Thanks in advance
riccardo

[Bob Phillips]

Sub Auto1()
Dim numcells As Long

    numcells = InputBox("How many cells to copy?")
    Range("K3").Resize(numcells).Copy
    Range("AP4").PasteSpecial Paste:=xlPasteValues
    Range("K15").Resize(numcells).Copy
    Range("AQ4").PasteSpecial Paste:=xlPasteValues
End Sub

[mancubus]

posting a workbook will help us understandand the requirement.
provide an input sheet with current data. and prepare an output sheet by manually copying the desired cells.

it is not a good practice making the user input the cell addresses for multiple copy-paste operations.
try to find the common points regarding the cells which will be copied.

example: if any cell in column K contains the value "copy me" the copy this cell including 2 cells beneath it to corresponding rows in column AP. (if it is K10, copy K10:K12 to AP10:AP12

example: if any cell value in column K is greater than 10, copy this cell including 2 cells below it to corresponding rows in column AP. (if it is K75, copy K75:K77 to AP75:AP77

etc etc

[RIC63]

Thanks everyone, i know that I have posted a 'non clear' question due -not only- to my poor english; the code proposed from xld works fine except that the lower position from which i must start to take cells values is k17 and I want go up (using xlUp ? ) of 3 cells up to K15.

I hope I was clear and I want thank you all anyway

[Bob Phillips]

If you know it starts at K17, and you want to go up to K15, why not just be explicit with K15:K17?

[RIC63]

Sorry for my delay in reply xld, i must start every time from that position because the quantity of data to take can vary, so one time can be that i must go up of 3 cells and the next time i must move of 5; thanks so much for your support now the routine works well
riccardo