Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to copy my data from different rows to some other column. Example.

B4 has to be copied to H129 ,
B25 to H130,
B46 to H131...and so on ..

Here the rows are getting incremented by 21 rows .

How do do that in excel ?

share|improve this question

3 Answers

up vote 0 down vote

Go to VBA and:

Sub sdgfsa()

    Dim i As Integer
    Dim j As Integer

    j = 129
    For i = 4 To 1000 Step 21 'put the end number you want, the start is 4 like your example
        Plan1.Cells(j, 8).Value = Plan1.Cells(i, 2).Value 'Here, plan1 represents the sheet, 2 is B column, 8 is H column
        'or you can use KazJaw's Copy here
        j = j + 1 'j gets incremented
    Next
End Sub
share|improve this answer
where do i write this script ... – user1896796 Apr 4 at 17:20
Go to the VBA editor (opens from Excel with Alt+F11). Paste it inside "ThisWorkbook" at the explorer on the left of the screen. Then run it from the VBA editor. Later you can assign the code to a button if it's needed to be run many times by users. – Daniel Apr 4 at 19:49
up vote 0 down vote

And when you need to copy all (i.e.. values and formats as well) than do it in this way:

Range("B4").Copy Range("H129")

Using @Daniel code you could need something like this inside your loop

'...see Daniel answer for beginning
Plan1.Cells(i, 2).Copy Plan1.Cells(j, 8)
'... and so on
share|improve this answer
up vote 0 down vote

Enter the following formula into cell H129:

=INDIRECT("b"&(ROW(H129)-129)*21+4)

Then use "Fill Down" to copy the formula from this cell into the cells below it.

This works because the "H129" reference will change to H130, H131, etc. as it is copied, so the ROW() function will return successively higher numbers.

Edit:

Corrected formula to match example in question.

share|improve this answer
this is not working ..it copies the content from a5... – user1896796 Apr 4 at 17:19
You're right. I corrected the formula so that it will work with the correct cells. – Tim Apr 4 at 21:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.