My code not working.. Please help!!

[Aashna]

Hello,

Please help me with this. I have a workbook with three tabs Raw Data, Master File and Category.

• There is a button 'Populate Sheet Data' embedded on the raw data sheet, upon clicking this button the master file should get filled with the data (as shown in 2nd table below) and should show a message box 'Process Finished'.
• Raw data sheet has 3 columns:

Data1: consists of fixed values
Article #: each article belong to one category or all (total categories are 4)
Category: Specifies to which category each article belongs (if it says all that particular article should appear in all 4 categories in master file sheet.


My code:

[/B][/COLOR]
[FONT=Verdana]Sub ArticleCategoryTransfer()[/FONT]
Dim j As Long, i As Long, lastRow1 As Long, DestLast As Long
Dim sh_data, sh_target As Worksheet
Dim AllCategory As String
Dim CategoryCount As Long


Set sh_data = Sheets("Raw Data")
Set sh_target = Sheets("Master File")
Set sh_category = Sheets("Category")


DestLast = sh_target.Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow1 = sh_data.UsedRange.Rows.Count
AllCategory = "All Category"
CategoryCount = sh_category.UsedRange.Rows.Count


'This loop populates data for individual category (category that aren't "All")


    For i = 2 To lastRow1
    
        If sh_data.Cells(i, 3).Value <> AllCategory Then
            sh_target.Cells.Range("G" & DestLast) = sh_data.Cells(i, 2).Value
            sh_target.Cells.Range("J" & DestLast) = sh_data.Cells(i, 3).Value
            DestLast = sh_target.Range("G" & Rows.Count).End(xlUp).Row + 1
        End If


    Next i


'This loop populates data for "All"


For i = 2 To lastRow1
 
    For j = 2 To CategoryCount
   
        If sh_data.Cells(i, 3).Value = AllCategory Then
            sh_target.Cells.Range("G" & DestLast) = sh_data.Cells(i, 2).Value
            sh_target.Cells.Range("J" & DestLast) = sh_sites.Cells(j, 1).Value
            DestLast = sh_target.Range("G" & Rows.Count).End(xlUp).Row + 1
        End If


    Next j


Next i


MsgBox "Process Finished!"

 [COLOR=#333333]End Sub[/COLOR][COLOR=#000080][B]

Thank You, Aashna

<tbody>
</tbody>

 

[Aashna]

Below are look-alike of my workbook:
Raw Data

	A	B	C
1	Data1	Article #	Category
2	A	230	1
3	A	82	4
4	A	461	all
5	A	661	3
6	A	641	all
7	A	668	2

<tbody>
</tbody>

Master File: Current status of output

	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4	Data2	Data3	Data1	Data4	Data5	Data6	Article #	Data7	Data8	Category	Delivery Date
5			A				230			1
6			A				82			4
7			A				461			all
8			A				661			3
9			A				641			all
10			A				668			2

<tbody>
</tbody>

• Data in columns Data2, Data3, Data5 & Data7 should get auto-fill as the data in these columns remain stagnant.

______________________________________________________________________________________________________

Master File: Desired Output A B C D E F G H I J K 1 2 3 4 Data2 Data3 Data1 Data4 Data5 Data6 Article # Data7 Data8 Category Delivery Date 5 c b A d 230 e 1 8/14/2015 6 c b A d 461 e 1 8/14/2015 7 c b A d 641 e 1 8/14/2015 8 c b A d 668 e 2 8/14/2015 9 c b A d 461 e 2 8/14/2015 10 c b A d 641 e 2 8/14/2015 11 c b A d 661 e 3 8/14/2015 12 c b A d 461 e 3 8/14/2015 13 c b A d 641 e 3 8/14/2015 14 c b A d 82 e 4 8/14/2015 15 c b A d 461 e 4 8/14/2015 16 c b A d 641 e 4 8/14/2015 <tbody> </tbody>

<tbody>
</tbody>