copy data every nth row

I want to copy the data every nth row.

the below code is working fine but ("Name", "Date", "Sore") are coming in a single instead I need the to be updated in column

example
in C5 as name
in C6 as Date
in C7 as Score

Sub inserttexteveryonerow1()
    Dim Last As Integer
    Dim emptyRow As Integer
        Last = Range("C" & Rows.Count).End(xlUp).Row
    For emptyRow = Last To 2 Step -1
        If Not Cells(emptyRow, 4).Value = "" Then
            Rows(emptyRow).Resize(3).Insert
            Range(Cells(emptyRow, "C"), Cells(emptyRow, "C")).Value = Array("Name", "Date", "Sore")
        End If
    Next emptyRow
End Sub

Open in new window

NirvanalearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An example workbook would be useful
0
NirvanalearnerAuthor Commented:
Sorry about it Roy,

Currently the second sheet. Sorry actually four rows. not three rows

name
Date
Score
City
every-nth-row.xlsm
0
Roy CoxGroup Finance ManagerCommented:
You seem to have asked variations on this question several times. I've looked at probably four workbooks today but it's no clearer.

Don't keep asking variations of the question, please explain clearly what you need to do.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NirvanalearnerAuthor Commented:
I will delete other questions Roy and keep the right one.
0
NirvanalearnerAuthor Commented:
can I delete this question.

Question which says "Populate data in Excel and organise" is the right one
0
NirvanalearnerAuthor Commented:
to be able to avoid confusion i will delete other questions
0
Roy CoxGroup Finance ManagerCommented:
Can you explain clearly what the code is supposed to do.
0
NirvanalearnerAuthor Commented:
Sure.

I have a Source file which I need to populate the data into the destination file which will be used as a template to an application feed.

I have attached the file here once you receive the file I will be able to explain you
application-sample-for-EE.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I have downloaded the new file and still cannot see what you need.
0
NirvanalearnerAuthor Commented:
0
NirvanalearnerAuthor Commented:
Sir I have updated the ppt. Please do let me know if I at still unclear. I was trying to breakdown the code that is the reason asking so many questions

1. I have tried and wrote some code to get the data from actual file
2. Insert 4 rows for every active cell in Column B of the destination file
3. Update by if Cell value is 2 in column A of Destination file the update formula
4.  Update by if Cell value is 4 in column A of Destination file then offset three rows to pick the row 1
5.if Cell values 2,3,4,5 update cell values in column C as hard coded
0
Roy CoxGroup Finance ManagerCommented:
I'll see what I can do
0
NirvanalearnerAuthor Commented:
Thank you very much.
0
Roy CoxGroup Finance ManagerCommented:
Why do you want the rows to be coloured?
0
NirvanalearnerAuthor Commented:
The colors are just for explanations
0
Roy CoxGroup Finance ManagerCommented:
This adds the empty rows.

I'm not sure about this
application-sample-for-EE-1-.xlsm
0
NirvanalearnerAuthor Commented:
Thank you. It works for adding rows but what I was looking for is like the link code in link below what the code in link below  Searches for Column A, I need column C, and it adds data in Columns I need the data to be in the same column in rows  

for example the code adds data in

B1, C1 and D1

the data that I needs to be in B1
B2
B3

https://www.extendoffice.com/documents/excel/2751-excel-insert-text-every-other-row.html
0
Roy CoxGroup Finance ManagerCommented:
0
NirvanalearnerAuthor Commented:
Yes. Exactly. Thank you very much
0
Roy CoxGroup Finance ManagerCommented:
That's good. Replace the code with this improved version

Option Explicit
Sub AddRows()

    Dim rRng As Range
    Dim lRw As Long
    Dim iX As Integer, iJ As Integer

    lRw = 8
    iJ = Sheets("Destination File").Cells(lRw, 2).End(xlDown).Row - 7

    Set rRng = Sheets("Destination File").Cells(8, 2)
    Do While rRng.Value <> ""
        Set rRng = rRng.Offset(1)

        rRng.Offset(1).Resize(4, 20).ClearFormats
        For iX = 1 To iJ
            rRng.EntireRow.Insert
        Next

        Range(rRng.Offset(-iJ), rRng.Offset(-1)).Value = Application.WorksheetFunction.Transpose(Array("A", "B", "C", "D"))

    Loop
End Sub

Open in new window


Did you delete the duplicated questions?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanalearnerAuthor Commented:
Not yet sir. This is for One column

I still have 3 more issues to be solved. The code you have provided is actually for the column "Dst_Head 2", Which I have changed to column 3

for "Dst_Head 1"  the data comes from " Source file" sheet every row 2, 3,4 needs to be copied from Source file and 5 and 1 needs to be same number which come from Column U6 for "Source file


Option Explicit
Sub AddRows()

    Dim rRng As Range
    Dim lRw As Long
    Dim iX As Integer, iJ As Integer

    lRw = 8
    iJ = Sheets("Destination File").Cells(lRw, 3).End(xlDown).Row - 7

    Set rRng = Sheets("Destination File").Cells(8, 3)
    Do While rRng.Value <> ""
        Set rRng = rRng.Offset(1)

        rRng.Offset(1).Resize(4, 20).ClearFormats
        For iX = 1 To iJ
            rRng.EntireRow.Insert
        Next

        Range(rRng.Offset(-iJ), rRng.Offset(-1)).Value = Application.WorksheetFunction.Transpose(Array("A", "B", "C", "D"))

    Loop
End Sub

Open in new window

application-sample-for-EE-2-.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I think that you should close this question then continue with the next problem. Let me know which question it is.

When you say source file, do you mean source sheet?
0
NirvanalearnerAuthor Commented:
Yes sir. when I say source file it is Source sheet.
0
Roy CoxGroup Finance ManagerCommented:
Which question is the next one to look at?
0
NirvanalearnerAuthor Commented:
Sir,

How Do I change this to start from row 16 instead of 8?

Option Explicit
Sub AddRows()

    Dim rRng As Range
    Dim lRw As Long
    Dim iX As Integer, iJ As Integer

    lRw = 8
    iJ = Sheets("Destination File").Cells(lRw, 2).End(xlDown).Row - 7

    Set rRng = Sheets("Destination File").Cells(8, 2)
    Do While rRng.Value <> ""
        Set rRng = rRng.Offset(1)

        rRng.Offset(1).Resize(4, 20).ClearFormats
        For iX = 1 To iJ
            rRng.EntireRow.Insert
        Next

        Range(rRng.Offset(-iJ, 1), rRng.Offset(-1, 1)).Value = Application.WorksheetFunction.Transpose(Array("A", "B", "C", "D"))

    Loop
End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Why are you changing it now?  i'm working on the same set up for the other code.
0
NirvanalearnerAuthor Commented:
It will be an import template for an application. If we have upload details like user. Date time etc. Its better to leave some rows
0
Roy CoxGroup Finance ManagerCommented:
Then let's get the code finished first then change the actual file and code as required
0
NirvanalearnerAuthor Commented:
Sure sir
0
Roy CoxGroup Finance ManagerCommented:
Check the code for the other question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.