HI
I have a table example below
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
At the moment, upon any update on another tab, it's replacing column A (client list) which is fine since C-F are formulas so will read column A and update.
HOWEVER
columns G-H user may have already inputted so I need it to keep those values according to the client listed in column A
and then sort by Column A (A-Z)
Here's my original code:
Am really grateful for anyone who can help me do this as I am at a loss?
many thanks
I have a table example below
Client | No. of acounts | Control Clients | Product1 | Product 2 | Product 3 | Proposed Product | Proposed Product |
code adds | (formula) | (formula) | (formula) | (formula) | (Formula) | (user selects) | (user writes text) |
Test1 | 1 | Yes | None | Yes | Yes | ||
Test2 | 1 | Yes | None | Yes | Yes |
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
At the moment, upon any update on another tab, it's replacing column A (client list) which is fine since C-F are formulas so will read column A and update.
HOWEVER
columns G-H user may have already inputted so I need it to keep those values according to the client listed in column A
and then sort by Column A (A-Z)
Here's my original code:
Code:
Sub TechFootPrint()
UnProtect
RemoveFilters1a
Dim TFtPrint As Worksheet
Dim ABC As Worksheet
Dim copyRange As Range
Dim lastrow As Long
Application.ScreenUpdating = False
Set ABC = Sheets("ABC Clients ")
Set TFtPrint = Sheets("Tech FootPrint")
ABC.Range("B3").ListObject.ShowTotals = False
TFtPrint.Range("B5").ListObject.ShowTotals = False
With Sheets("Tech FootPrint")
.Range("Table16[Client]").ClearContents
End With
ABC.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
"<>*Lost*", Operator:=xlAnd, Criteria2:="<>*OOS*"
ABC.Range("Table6[[ABC Client]]").SpecialCells(xlCellTypeVisible).Copy
TFtPrint.Range("B5").PasteSpecial xlPasteValues
Application.CutCopyMode = False
With ActiveWorkbook.Worksheets("Tech FootPrint")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B5").Resize(lastrow - 3), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort.SetRange .Range("B5").Resize(lastrow - 3)
.Sort.Header = xlNo
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
RemoveFilters1a
ABC.Range("B3").ListObject.ShowTotals = True
TFtPrint.Range("B5").ListObject.ShowTotals = True
Protect
End Sub
Am really grateful for anyone who can help me do this as I am at a loss?
many thanks
Last edited: