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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,435
Office Version
365
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
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
 

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
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
Thanks Fluff, that worked! with a little bit of adjusting that did the trick
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,435
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,435
Office Version
365
Platform
Windows
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
 

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,435
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,078,285
Messages
5,339,287
Members
399,291
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top