Hi,

I have 5000 names in a excel sheet and after every name there is blank row,
i need macro to copy the DEPT and CCentre data to all the names example as
shown below

Name DEPT CCENTRE
A1 A P
A1 S U
A1 D Y
A1 R K

B2 A P
B2 S U
B2 D Y
B2 R K
B2

Hi All,

Please Tell Me [ How to open Hidden excel Sheet But Only I Follow the
Hyperlink ]
& Please Send me Screen Shot ok

Thanks
Abhijeet

Hi Ranjith-


If I understand your issue correctly, there are two options for doing this:


(1)Highlight the cells you want to copy including a row of empty cells and
drag this until the end of your document.

(2)If all the data you want to copy is found in “B2:C5” and there are four
names between each space the following code should work:
Option Explicit
Dim X As Long


Sub RepeatCopy()

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

X = 0

Do

Range("b7").Offset(5 * X, 0).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
' Selection.End(xlDown).Select
X = X + 1
Loop Until IsEmpty(ActiveCell.Offset(2, 0))

End Sub


"Ranjith Kurian" wrote:

> Hi,
>
> I have 5000 names in a excel sheet and after every name there is blank row,
> i need macro to copy the DEPT and CCentre data to all the names example as
> shown below
>
> Name DEPT CCENTRE
> A1 A P
> A1 S U
> A1 D Y
> A1 R K
>
> B2 A P
> B2 S U
> B2 D Y
> B2 R K
> B2
>