Copy x rows y times and then insert customer ID on each set of x rows [Solved]

Question

Copy x rows y times and then insert customer ID on each set of x rows [Solved]

Report

Y7654

Posts: 2
Registration date: Tuesday December 31, 2019
Status: Member
Last seen: December 31, 2019

- Updated on Dec 31, 2019 at 11:08 AM
TrowaD

Posts: 2670
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: October 22, 2020

- Jan 14, 2020 at 11:44 AM

Hello, I have a set of criteria, 56 criteria as of now, and I have to check how many criteria each customer meets.
In one tab I have the list of the 56 criteria in reach row plus the heading row
In another tab I have a download list of rows where each customer has a row for every criteria they currently have
I plan to have an additional tab where I copy the customer list and remove duplicates so it'll be my alpha

What I want to do:
On the tab that has the 56 criteria, I want to copy it the number of times as there are customers and insert a page break after each new customer.
So, rows 2-57 will be for customer 1, rows 58-114 will be for customer 2, etc.
Then I want to add a column that adds customer ID 1 on rows 2-57, customer ID 2 on rows 58-114, until the end.

I'll them vlookup the criteria to determine who has met what...

Any help or suggestions is greatly appreciated!

Copy x rows y times and then insert customer ID on each set of x rows
Insert custom watermark on one page only ✓ - Forum - Word
Excel - Copy and insert rows n number of times - How-To - Excel
Excel - Copy row and insert n times - How-To - Excel
Need Macro- Copy Row and Insert 10 times ✓ - Forum - Excel
Text to n rows + copy row and insert n times ✓ - Forum - Excel

1 reply

Reply Ask a question

TrowaD · Accepted Answer · 2020-01-14T11:44:17-0500

Answer 1 / 1

TrowaD

Posts: 2670
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: October 22, 2020

446
Jan 14, 2020 at 11:44 AM

Hi Y7654,

Give the following code a try:

Sub RunMe()
Dim lRow, x, nID As Integer

lRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
nID = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Rows.Count

Sheets("Sheet2").Range("A2:A" & nID + 1).Copy

For x = lRow To 2 Step -1
    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Next x

For Each cell In Sheets("Sheet3").Range("A2:A" & lRow)
    cell.Copy
    For x = nID To 1 Step -1
        Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Next x
Next cell
End Sub

Best regards,
Trowa

Your reply