VBA/Macros for shifting entries from one workbook to other

[mrinal354]

I have an excel sheet which has a lot of entries of different companies. I have another workbook in which I want to transfer all the entries of a single company ex: Microsoft against which "open"is mentioned in a column. I want to keep updating the new workbook as and when new entries are made. How to directly transfer and keep updating using vba and macros?

 

[offthelip]

When trying to keep a list updated it can be hard to check which entries already exist, it is often quicker and easier to just regenerate the list, The following code does that. You haven't give any details as to the layout or the names of your workbooks , worksheets or anything, so I have written code that checks for Microsoft in Column A of the active sheet and "open2 in column D nad then copies it to sheet 1 of the workbook "Book2" which is assumed to already be open

Sub test()
Dim outarr()
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, lastcol))
ReDim outarr(1 To lastrow, 1 To lastcol)
Workbooks("Book2").Activate
With Worksheets("Sheet1")
 .UsedRange.Clear
 indi = 1
 For i = 1 To lastrow
  If i = 1 Or (inarr(i, 1) = "Microsoft" And inarr(i, 4) = "open") Then  ' check if title row or Micorsoft in col A and Open in col D
   For j = 1 To lastcol
    outarr(indi, j) = inarr(i, j)    ' copy a row
   Next j
   indi = indi + 1  ' increment output row
  End If
 Next i
 .Range(.Cells(1, 1), .Cells(indi, lastcol)) = outarr ' write the output
End With
End Sub

hopefully you can modifiy this to do what you really want

 

[mrinal354]

Thank you so much