Make Duplicate Data in same sheet

[Deepak Sharma]

Hi All,
I have data range from A1 : J6, I need to paste it multiple times (85 times) in same sheet giving one row's GAP only.

Please advise how to proceed this.

Regards,

 

[Marc L]

Hi !

Use absolute reference in your formula for your first "copy".
Then select this "new copy" including the gap then copy
then select destination range and paste …
​

 

[Deepak Sharma]

Thanks but Sorry didn't understand bro!

 

[Marc L]

So see Excel inner help.

In your formula any cell reference must be with $ char,
example for cell A1 reference the formula syntax is =$A$1 …
​

 

[Deepak Sharma]

Not an issue buddy, Thanks for your advice & help.
However, I did below coding to resolved it

Sub Duplicate()
Dim i As Integer
i = 1
Do Until i > 5
Sheet1.Range("A1").Select
Sheet1.Range(ActiveCell, Selection.End(xlDown)).Select
Sheet1.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheet1.Range("A1048576").End(xlUp).Offset(2, 0).Select
ActiveCell.PasteSpecial
i = i + 1
Loop
End Sub

 

[Marc L]

As here it is not the VBA forum (Excel formula !)
so next time create your thread in the appropriate forum
and use the appropriate icon to post a code, thanks …​

 

[Deepak Sharma]

I never asked for VBA code here, I was only expecting excel formula, But when no reply came than, I had to create VBA code & just let u knew about that I have created it.
Thanks !

 

[bosco_yip]

Perhaps,

Method (1)

1.1] In A8, enter formula

1.1]=OFFSET(A$1,MOD(ROW(A1)-1,7),)

and A8 >> Custom cell format: 0;;;@

all copied across to Col J and down

Method (2)

2.1] In A8 enter : =A1

2.2] In A9 enter : =A2

and A8 and A9 >> Custom cell format: 0;;;@

2.3] Select A8 and A9, copied across to Col J and all down to row 595.

Regards
Bosco

 

[Deepak Sharma]

Thank you so much Bosco ! This is what exactly I was looking for !!!

Thanks Buddy !