Excel VBA script to populate another sheet conditionally

Question

I would like to write a VBA macro for excel through which i want data from a master sheet to populated to another sheets conditionally.

for example, my master sheet ("Sheet1) has multiple rows and column. The condition for data population from Sheet1 to Sheet2 should be based on these condition

(1) Only rows which has a particular string in a column (say "keyword" string in column D)

(2) Only few columns to be copied from Sheet1 to Sheet2 (say column A,B,E & G)

Dani El Dani El · Accepted Answer · 2016-02-25T07:09:56

I have a code that copies a column when the heading of the column is a certain string, would that help?
Edit1:
Here is what I have come up with. The code should be flexible enough to adapt to any type of spreadsheet you've got

Dim keyColumn As Integer
Dim i As Integer
Dim keyWord As Variant 'I've used variant, so you can choose your own data type for the keyword
Dim dataSh As String 'I'm using sheet names for sheet referencing
Dim populateSh As String
Dim rowNum As Integer
Dim dataRow() As Variant

Sub Populate()
'set the column number, which contains the keywords, the keyword itself,
'name of the sheet to populate and the row offset you'd like to start populating
    populateSh = "populate"
    keyColumn = 4
    keyWord = "yes"
    rowNum = 0
'assuming you run the macro in the sheet you get the data from, get its name to return to it after copying the row
    dataSh = ActiveSheet.Name
'loop through all the used cells in the column
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
        If Cells(i, keyColumn) = keyWord Then
'starting in row 1 in the sheet you populate, you'll have to set the rowNum variable to desired offset few lines above
            rowNum = rowNum + 1
            Call copyRow(i, rowNum)
        End If
    Next i
End Sub

Sub copyRow(ByVal cRow As Integer, ByVal pRow As Integer)
    Dim colNum As Integer
'set the number of columns you'd like to copy
    colNum = 3
'redimension the array to carry the data to other sheet
'this can be done any way you,d like, but I'm using array for flexibility
    ReDim dataRow(1 To colNum)
'put the data into the array, as an example I'm using columns 1, 2 and 3, while skipping the keyword column.
    dataRow(1) = Cells(cRow, 1)
    dataRow(2) = Cells(cRow, 2)
    dataRow(3) = Cells(cRow, 3)

    Sheets(populateSh).Select
        For p = 1 To UBound(dataRow)
            Cells(pRow, p) = dataRow(p)
        Next p
    Sheets(dataSh).Select
End Sub

Hope that helps. Sorry for any style errors in advance

Excel VBA script to populate another sheet conditionally

1 Answers