Nirvana
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
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
An example workbook would be useful
ASKER
Sorry about it Roy,
Currently the second sheet. Sorry actually four rows. not three rows
name
Date
Score
City
every-nth-row.xlsm
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.
Don't keep asking variations of the question, please explain clearly what you need to do.
ASKER
I will delete other questions Roy and keep the right one.
ASKER
can I delete this question.
Question which says "Populate data in Excel and organise" is the right one
Question which says "Populate data in Excel and organise" is the right one
ASKER
to be able to avoid confusion i will delete other questions
Can you explain clearly what the code is supposed to do.
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 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.
ASKER
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
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
ASKER
Thank you very much.
Why do you want the rows to be coloured?
ASKER
The colors are just for explanations
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
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
Like this?
application-sample-for-EE-1-.xlsm
application-sample-for-EE-1-.xlsm
ASKER
Yes. Exactly. Thank you very much
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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?
When you say source file, do you mean source sheet?
ASKER
Yes sir. when I say source file it is Source sheet.
Which question is the next one to look at?
ASKER
ASKER
Sir,
How Do I change this to start from row 16 instead of 8?
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
Why are you changing it now? i'm working on the same set up for the other code.
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
ASKER
Sure sir
Check the code for the other question