CODEWITHSUNDEEP
excelvba
Amit Moses Albert
Amit Moses Albert

Reputation: 123

populating sheet2 using macros

I am an excel novice. I am trying to write macros to populate sheet2 based on sheet one. I have following columns on sheet1: Name CustomName CustomeValue a Bay 11 a Site UK a Rack 3 b Site UK b Rack 2 C empty empty

Sheet 2 - output should be as follows Name Bay Site Rack a 11 UK 3 b UK 2 c

I did try to write macros to match row by row and compare and populate selectively, but the script randomly populates when huge date comes in, Any help on this would be highly appreciated.

Code snippet:

Sub populatingsheet2()

    x = 2
    y = 2 
    Sheet2.Cells(y, 1) = Sheet1.Cells(x, 1) 
    Do While x <= 4 

        If Sheet1.Cells(x, 1) = Sheet1.Cells(x + 1, 1) Then 

            ‘I want unique records 
            'MsgBox "Identical" 
            If Sheet1.Cells(x, 2) = "Bay" Then 
                Sheet2.Cells(y, 2) = Sheet1.Cells(x, 3) 
            End If 
            If Sheet1.Cells(x, 2) = "Site" Then 
                Sheet2.Cells(y, 3) = Sheet1.Cells(x, 3) 
            End If 
            If Sheet1.Cells(x, 2) = "Rack" Then 
                Sheet2.Cells(y, 4) = Sheet1.Cells(x, 3) 
            End If 

        Else: 'MsgBox "Not Identical" 
        End If 
        x = x + 1 
        y = y + 1 

    Loop 

End Sub

Upvotes: 0

Views: 355

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

so, have you considered you could do all that using Formulas on Sheet 2 without a macro?

maybe like this in Sheet 2 Colummn B:

=IF(Sheet1!B:B="Bay",Sheet1!C:C,"")

in column C

=IF(Sheet1!B:B="Site",SHeet1!C:C,"")

hth

Philip

Upvotes: 1

Related Questions