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)
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
