VBA copy and paste down a column with offset

[jacinthn]

i have a formula that copies a range down a column as many times as i need, however i need to insert a gap or offset between each paste . this does it but it removes my header row and i cant figure out how to get it to do the offset and paste without removing the header each time... help please

Sub PasteDownFormat()
Dim x As Long, i As Long
x = x = x = i
x = InputBox("How many times")
If x < 1 Then Exit Sub
With Range("B1:B1243")
For i = 1 To x
Cells(i * 1243 + 1, 2).Resize(.Rows.Count + 1, .Columns.Count).Offset(1, 0).Value = .Offset(1, 0).Value
Next
End With
End Sub

 

[JoeMo]

i have a formula that copies a range down a column as many times as i need, however i need to insert a gap or offset between each paste . this does it but it removes my header row and i cant figure out how to get it to do the offset and paste without removing the header each time... help please

Sub PasteDownFormat()
Dim x As Long, i As Long
x = x = x = i
x = InputBox("How many times")
If x < 1 Then Exit Sub
With Range("B1:B1243")
For i = 1 To x
Cells(i * 1243 + 1, 2).Resize(.Rows.Count + 1, .Columns.Count).Offset(1, 0).Value = .Offset(1, 0).Value
Next
End With
End Sub

If the header is in B1 try changing this:

Cells(i * 1243 + 1, 2).Resize(.Rows.Count + 1, .Columns.Count).Offset(1, 0).Value = .Offset(1, 0).Value

to this:

Cells(i * 1243 + 1, 2).Resize(.Rows.Count + 1, .Columns.Count).Offset(1, 0).Value = .Value

 

[jacinthn]

Thanks for the reply Joe when I try that it offsets the very first one it pastes then all the other ones are together with no gap/ offset between them

 

[JoeMo]

Thanks for the reply Joe when I try that it offsets the very first one it pastes then all the other ones are together with no gap/ offset between them

This should do it:

Sub PasteDownFormat()
Dim x As Long, i As Long
x = InputBox("How many times")
If x < 1 Then Exit Sub
With Range("B1:B10")
For i = 1 To x
Cells(i * 10 + i, 2).Resize(.Rows.Count, .Columns.Count).Offset(1, 0).Value = .Value
Next
End With
End Sub

 

[jacinthn]

Brilliant!!! thanks mate works like a charm. really appreciate the help.

Thank you!!!!!!!!!!!!!