• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42
  • Last Modified:

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

0
Nirvana
Asked:
Nirvana
  • 17
  • 14
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
An example workbook would be useful
0
 
NirvanamanagerAuthor 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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Question which says "Populate data in Excel and organise" is the right one
0
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor Commented:
0
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor Commented:
Thank you very much.
0
 
Roy CoxGroup Finance ManagerCommented:
Why do you want the rows to be coloured?
0
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor 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
 
NirvanamanagerAuthor Commented:
Sure sir
0
 
Roy CoxGroup Finance ManagerCommented:
Check the code for the other question
0

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now