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

Report
Posts
2
Registration date
Tuesday December 31, 2019
Status
Member
Last seen
December 31, 2019
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
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!

1 reply

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!