VBA Code to Find Copy Rows From Source Sheet Based on Text and Paste on Target Sheet Corresponding Number of Times.

[JPatty]

I have a workbook with three sheets. The first sheet "Blanket_Data" has all of the standard colors that can be ordered by a customer. The second sheet "Cust_Color" contains the colors ordered by customers in column A and the customer account in column B. I need to find and copy the row on the Blanket_Data sheet based on the text in each cell in column A on the Cust_Color sheet, starting on A2. The row copied from Blanket_Data needs to be pasted on the third sheet "Import" and copied down for the number of cells with text in column A on the Cust_Color sheet, starting at A2. The customer account from column B on Cust_Color needs to be copied to the Import sheet in columns C and D. The Import sheet below is an example of what I would like the final result to be, sans the colorful formatting.
Thank you in advance.

BLANKET_DATA

COLOR	TYPE	CUST ACCT	CUST CODE	COLOR CODE	Username 1	Credential 1	Credential 2	Credential 3	ACCT	ACCOUNT FLAG	Payment	ENABLE OVERRIDE
BLACK	STANDARD			BLACK	SPECIAL	12345			888888		USD
BLUE	STANDARD			BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BROWN	STANDARD			BROWN					444444		USD	No
GRAY	STANDARD			GRAY		12345			999999		USD	No
GREEN	STANDARD			GREEN	SPECIAL	12345			666666		USD	No
ORANGE	STANDARD			ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD			PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD			PURPLE	SPECIAL	67891			222222		USD	No
RED	STANDARD			RED	SPECIAL	12345			777777		USD
WHITE	STANDARD			WHITE					555555		USD
YELLOW	STANDARD			YELLOW			11111		555555		USD	No

CUST_COLOR

COLOR	CUST ACCT
GREEN	TPFB
YELLOW	YPHPC
BLUE	JPKCJ
BLACK	KPPM
RED	VPTFMI
ORANGE	BPWPC
PINK
PURPLE

IMPORT

COLOR	TYPE	CUST ACCT	CUST CODE	COLOR CODE	Username 1	Credential 1	Credential 2	Credential 3	ACCT	ACCOUNT FLAG	Payment	ENABLE OVERRIDE
GREEN	STANDARD	TPFB	TPFB	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	TPFB	TPFB	YELLOW			11111		555555		USD	No
BLUE	STANDARD	TPFB	TPFB	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	TPFB	TPFB	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	TPFB	TPFB	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	TPFB	TPFB	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	TPFB	TPFB	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	TPFB	TPFB	PURPLE	SPECIAL	67891			222222		USD	No
GREEN	STANDARD	YPHPC	YPHPC	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	YPHPC	YPHPC	YELLOW			11111		555555		USD	No
BLUE	STANDARD	YPHPC	YPHPC	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	YPHPC	YPHPC	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	YPHPC	YPHPC	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	YPHPC	YPHPC	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	YPHPC	YPHPC	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	YPHPC	YPHPC	PURPLE	SPECIAL	67891			222222		USD	No
GREEN	STANDARD	JPKCJ	JPKCJ	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	JPKCJ	JPKCJ	YELLOW			11111		555555		USD	No
BLUE	STANDARD	JPKCJ	JPKCJ	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	JPKCJ	JPKCJ	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	JPKCJ	JPKCJ	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	JPKCJ	JPKCJ	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	JPKCJ	JPKCJ	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	JPKCJ	JPKCJ	PURPLE	SPECIAL	67891			222222		USD	No
GREEN	STANDARD	KPPM	KPPM	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	KPPM	KPPM	YELLOW			11111		555555		USD	No
BLUE	STANDARD	KPPM	KPPM	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	KPPM	KPPM	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	KPPM	KPPM	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	KPPM	KPPM	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	KPPM	KPPM	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	KPPM	KPPM	PURPLE	SPECIAL	67891			222222		USD	No
GREEN	STANDARD	VPTFMI	VPTFMI	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	VPTFMI	VPTFMI	YELLOW			11111		555555		USD	No
BLUE	STANDARD	VPTFMI	VPTFMI	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	VPTFMI	VPTFMI	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	VPTFMI	VPTFMI	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	VPTFMI	VPTFMI	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	VPTFMI	VPTFMI	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	VPTFMI	VPTFMI	PURPLE	SPECIAL	67891			222222		USD	No
GREEN	STANDARD	BPWPC	BPWPC	GREEN	SPECIAL	12345			666666		USD	No
YELLOW	STANDARD	BPWPC	BPWPC	YELLOW			11111		555555		USD	No
BLUE	STANDARD	BPWPC	BPWPC	BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
BLACK	STANDARD	BPWPC	BPWPC	BLACK	SPECIAL	12345			888888		USD
RED	STANDARD	BPWPC	BPWPC	RED	SPECIAL	12345			777777		USD
ORANGE	STANDARD	BPWPC	BPWPC	ORANGE	SPECIAL	67891		22	111111		USD	No
PINK	STANDARD	BPWPC	BPWPC	PINK	SPECIAL	67891	33333		333333		USD	No
PURPLE	STANDARD	BPWPC	BPWPC	PURPLE	SPECIAL	67891			222222		USD	No

 

[DanteAmor]

First, let me thank you for taking the time to set such an explicit example.
I hope the wait and your effort are worth it.

Try the following:

Sub Generate_import_sheet()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim rColor As Range, rCust As Range
  Dim i As Long
  Dim f As Range
  
  Set sh1 = Sheets("BLANKET_DATA")
  Set sh2 = Sheets("CUST_COLOR")
  Set sh3 = Sheets("IMPORT")
  sh3.Rows("2:" & Rows.Count).ClearContents
  
  i = 2
  For Each rCust In sh2.Range("B2", sh2.Range("B" & Rows.Count).End(3))
    For Each rColor In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3))
      Set f = sh1.Range("A:A").Find(rColor.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        sh3.Range("A" & i).Resize(1, 13).Value = sh1.Range("A" & f.Row).Resize(1, 13).Value
        sh3.Range("B" & i).Resize(1, 2).Value = rCust.Value
        i = i + 1
      End If
    Next
  Next
End Sub

 

[JPatty]

First, let me thank you for taking the time to set such an explicit example.
I hope the wait and your effort are worth it.

Try the following:

Sub Generate_import_sheet()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim rColor As Range, rCust As Range
  Dim i As Long
  Dim f As Range
 
  Set sh1 = Sheets("BLANKET_DATA")
  Set sh2 = Sheets("CUST_COLOR")
  Set sh3 = Sheets("IMPORT")
  sh3.Rows("2:" & Rows.Count).ClearContents
 
  i = 2
  For Each rCust In sh2.Range("B2", sh2.Range("B" & Rows.Count).End(3))
    For Each rColor In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3))
      Set f = sh1.Range("A:A").Find(rColor.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        sh3.Range("A" & i).Resize(1, 13).Value = sh1.Range("A" & f.Row).Resize(1, 13).Value
        sh3.Range("B" & i).Resize(1, 2).Value = rCust.Value
        i = i + 1
      End If
    Next
  Next
End Sub

Hi Dante,

That code worked great! Thank you so much. I didn't realize it at the time but I need to find and copy the row on the "Blanket_Data" sheet based on the text in each cell in col A as well as col B on the Cust_Color sheet. Any help would be appreciated.

Cust_Color

API_Credentials.xlsm
	A	B	C	D
1	COLOR	Type	CUST ACCT
2	GREEN	Standard	TPFB
3	YELLOW	Standard	YPHPC
4	BLUE	Standard	JPKCJ
5	BLACK	Standard	KPPM
6	RED	Standard	VPTFMI
7	ORANGE	Standard	BPWPC
8	PINK	Standard
9	PURPLE	Vivid
10
Sheet1

Blanket_Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	COLOR	TYPE	CUST ACCT	CUST CODE	COLOR CODE	Username 1	Credential 1	Credential 2	Credential 3	ACCT	ACCOUNT FLAG	Payment	ENABLE OVERRIDE
2	BLACK	STANDARD			BLACK	SPECIAL	12345			888888		USD
3	BLUE	STANDARD			BLUE	SPECIAL	67891	22222		777777	No	USD	Yes
4	BROWN	STANDARD			BROWN					444444		USD	No
5	GRAY	STANDARD			GRAY		12345			999999		USD	No
6	GREEN	STANDARD			GREEN	SPECIAL	12345			666666		USD	No
7	ORANGE	STANDARD			ORANGE	SPECIAL	67891		22	111111		USD	No
8	PINK	STANDARD			PINK	SPECIAL	67891	33333		333333		USD	No
9	PURPLE	STANDARD			PURPLE	SPECIAL	67891			222222		USD	No
10	RED	STANDARD			RED	SPECIAL	12345			777777		USD
11	WHITE	STANDARD			WHITE					555555		USD
12	YELLOW	STANDARD			YELLOW			11111		555555		USD	No
13	PURPLE	Vivid			YELLOW			11111		555555		USD	No
14
Sheet2