Link to home
Start Free TrialLog in
Avatar of Nirvana
NirvanaFlag for India

asked on

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

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

An example workbook would be useful
Avatar of Nirvana

ASKER

Sorry about it Roy,

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

name
Date
Score
City
every-nth-row.xlsm
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.
Avatar of Nirvana

ASKER

I will delete other questions Roy and keep the right one.
Avatar of Nirvana

ASKER

can I delete this question.

Question which says "Populate data in Excel and organise" is the right one
Avatar of Nirvana

ASKER

to be able to avoid confusion i will delete other questions
Can you explain clearly what the code is supposed to do.
Avatar of Nirvana

ASKER

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
I have downloaded the new file and still cannot see what you need.
Avatar of Nirvana

ASKER

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
I'll see what I can do
Avatar of Nirvana

ASKER

Thank you very much.
Why do you want the rows to be coloured?
Avatar of Nirvana

ASKER

The colors are just for explanations
This adds the empty rows.

I'm not sure about this
application-sample-for-EE-1-.xlsm
Avatar of Nirvana

ASKER

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
Avatar of Nirvana

ASKER

Yes. Exactly. Thank you very much
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nirvana

ASKER

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
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?
Avatar of Nirvana

ASKER

Yes sir. when I say source file it is Source sheet.
Which question is the next one to look at?
Avatar of Nirvana

ASKER

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

Why are you changing it now?  i'm working on the same set up for the other code.
Avatar of Nirvana

ASKER

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
Then let's get the code finished first then change the actual file and code as required
Avatar of Nirvana

ASKER

Sure sir
Check the code for the other question