PDA

View Full Version : [SOLVED] Code for pasting according to criteria



Veeru
09-07-2017, 10:58 AM
Hi,

I looking for a code for to add fund number in number of tabs at same time.
Sample book attached, in last tab I have mentioned in which tab and Column we need to enter fund number
Fund number to be added at last of existing data in all tabs.
One special tab NCF data requires one criteria mentioned in Col. A2. So let’s say fund number related to “AGI DSS Prelim” should be added in “AGI DSS Prelim” group in NCF data tab by creating a new row and in all other tabs just fund number at last of existing data .

Last thing can we create a code under Event procedure in “Calc Tool” sheet in Cell A2 & B2

Thanks as always..

SamT
09-07-2017, 11:48 AM
NCF data = Add new Row at bottom, then Sort by "Source.
All other tabs, Column "Source" = NCF data Column "Name." Which is = Calc Tool Column "Source?"
Is Calc Tool Column "Source" value to be added to other tabs?




can we create a code under Event procedure in “Calc Tool” sheet in Cell A2 & B2
Easiest way to add data to all other tabs.

Veeru
09-08-2017, 07:26 AM
Yes, value in A2 and B2 in Calc tool sheet should be added in NCF Data according to the group we have in A2 cell.

in all other tabs only fund number needs to be added at last

mdmackillop
09-08-2017, 09:24 AM
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim S, FN, sh As Worksheet, tgt As Range
On Error GoTo Exits
Application.EnableEvents = False
If Target.Address = "$B$2" Then
S = Target.Offset(, -1)
FN = Target
For Each sh In Worksheets
Select Case sh.Name
Case "FundName Rollup Mapping", "Prod Category Lookup", "Retail Flash NCF by Month"
Set tgt = sh.Cells(Rows.Count, 2).End(xlUp)(2)
tgt.Value = FN
Case "AuM_Data"
Set tgt = sh.Cells(Rows.Count, 1).End(xlUp)(2)
tgt.Value = FN
Case "NCF_Data"
Set tgt = sh.Columns(1).Find(S, after:=sh.Cells(1, 1), searchdirection:=xlPrevious)
tgt(2).EntireRow.Insert
tgt(2).Resize(, 3) = Array(S, "", FN)
End Select
Next
End If
Target.Offset(, -1).Resize(, 2).ClearContents
Exits:
Application.EnableEvents = True
End Sub

Veeru
09-08-2017, 12:10 PM
Yes it is working fine and perfect....thanks a ton