copying a header every nth row

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
Hi,

Is there a way to copy my header row (A1:J1) to every nth row? I'm creating a worksheet of individual tables and I need to get my one header to every 15th row. I don't want to 'insert copied cells' Id rather just a simple paste without moving all the data down one row each time.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Maybe something like
VBA Code:
Sub alanlambden()
    Dim i As Long
    
    For i = 15 To 30 Step 15
        Range("A1:J1").Copy Range("A" & i)
    Next i
End Sub
 
Upvote 0
Try this

VBA Code:
Sub copying_header()
  Dim i As Long
  Range("A1:J1").Copy
  For i = 15 To ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row Step 15
    Range("A" & i).PasteSpecial xlPasteAll
  Next
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You might be able to help me with this .. forgive me i'm still a novice with VBA but getting there. I want the macro to ask me what row Id like to copy, and then ask me on what regularly spaced intervals id like to copy it. i.e id like to copy the fourth row every 15 spaces .. or whatever. The reason is, there's more than just the header to copy. I think I've got close to it below, but it stops at the third last line. Any suggestions?


Sub CopyRow()
Dim i As Long
Dim copyhead As Integer
Dim pastehead As Integer

copyhead = Application.InputBox("Enter row interval. ")
pastehead = Application.InputBox("What nth row. ")

For i = 17 To 300 Step pastehead
Range("A & copyhead:J & copyhead").Copy Range("A" & i)
Next i
End Sub
 
Upvote 0
How about
VBA Code:
Sub alanlambden()
    Dim i As Long, CopyHead As Long, PasteHead As Long
    
    CopyHead = Application.InputBox("Enter row interval. ", , , , , , , 1)
    PasteHead = Application.InputBox("What nth row. ", , , , , , , 1)
    If CopyHead = 0 Or PasteHead = 0 Then
        MsgBox "One of the boxes was blank"
        Exit Sub
    End If
    For i = 17 To 300 Step PasteHead
        Range("A" & CopyHead).Resize(, 10).Copy Range("A" & i)
    Next i
End Sub
 
Upvote 0
How about
VBA Code:
Sub alanlambden()
    Dim i As Long, CopyHead As Long, PasteHead As Long
   
    CopyHead = Application.InputBox("Enter row interval. ", , , , , , , 1)
    PasteHead = Application.InputBox("What nth row. ", , , , , , , 1)
    If CopyHead = 0 Or PasteHead = 0 Then
        MsgBox "One of the boxes was blank"
        Exit Sub
    End If
    For i = 17 To 300 Step PasteHead
        Range("A" & CopyHead).Resize(, 10).Copy Range("A" & i)
    Next i
End Sub

Thanks man with some playing around that worked!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top