SOLVED
Home

Help make Formula talk to macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1262709%22%20slang%3D%22en-US%22%3EHelp%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262709%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20formula%3A%20%3DCOUNTA(%24B41%3A%24B3880)%2B40%3C%2FP%3E%3CP%3EAnd%20the%20following%20Macro%3A%3C%2FP%3E%3CP%3ESub%20CopySortCalcRSI()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20CopyRSITemp%20Macro%3CBR%20%2F%3E'%3C%2FP%3E%3CP%3E'%3CBR%20%2F%3ERange(%22BE3881%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EActiveWindow.ScrollRow%20%3D%2041%3CBR%20%2F%3ERange(%22BE41%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteFormulas%2C%20Operation%3A%3DxlNone%2C%20_%3CBR%20%2F%3ESkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22BE41%3ABE3043%22)%2C%20Type%3A%3DxlFillDefault%3CBR%20%2F%3ERange(%22BE41%3ABE3043%22).Select%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlAutomatic%3CBR%20%2F%3ERange(%22B41%3ABF3043%22).Select%3CBR%20%2F%3ERange(%22BF3043%22).Activate%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22BE41%3ABE3043%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B41%3ABF3043%22)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlManual%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BG14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22BI5%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ECall%20CleanRSIcolumn%3C%2FP%3E%3CP%3EEnd%20With%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20tells%20me%20that%20I%20have%203043%20populated%20cells%20in%20column%20B.%20The%20macro%20need%20to%20auto%20fill%203043%20cell.%20I%20have%20been%20manually%20telling%20the%20macro%20how%20many%20lines%20to%20auto%20fill.%20How%20can%20I%20get%20these%202%20code%20to%20talk%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1262709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262893%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20you%20have%20populated%20cells%20in%20column%20A41%20and%20down%20from%20there%20and%20you%20want%20your%20formula%20in%20the%20equivalent%20number%20of%20cells%20in%20column%20B.......in%20that%20case%2C%20I%20would%20identify%20the%20last%20row%20from%20column%20A%20and%20use%20this%20for%20the%20range%20to%20enter%20your%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22vbatext%22%3E'Find%20the%20last%20used%20row%20in%20a%20Column%3A%20column%20A%20in%20this%20example%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20Dim%20LastRow%20As%20Long%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20With%20ActiveSheet%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20LastRow%20%3D%20.Cells(.Rows.Count%2C%20%22A%22).End(xlUp).Row%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20then%20copy%20down%20a%20formula%20in%20B41%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20Range(%22B41%3AB%22%20%26amp%3B%20LastRow).Filldown%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262950%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262950%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20You%20Charla.%20Im%20a%20little%20confused%20however.%20I%20have%20data%20in%20row%20B%20therefor%20can%20not%20put%20formula%20in%20the%20cells.%20Second%2C%20Im%20not%20clear%20where%20I%20would%20plug%20in%20your%20code%20to%20mind.%20Would%20you%20kindly%20input%20your%20code.%20Do%20not%20consider%20row%20A.%20Thank%20you%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1263480%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263480%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20seems%20I%20got%20the%20wrong%20end%20of%20the%20stick%20-%20try%20this%2C%20which%20incorporates%20your%20COUNTA%20in%20the%20code%20so%20no%20longer%20required%20in%20the%20worksheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CopySortCalcRSI()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20CopyRSITemp%20Macro%3CBR%20%2F%3E'%3C%2FP%3E%3CP%3E'%3C%2FP%3E%3CP%3EDim%20rng%20As%20Range%3CBR%20%2F%3EDim%20lr%20As%20Integer%3CBR%20%2F%3ESet%20rng%20%3D%20Range(%22B41%3AB3880%22)%3CBR%20%2F%3Elr%20%3D%20WorksheetFunction.CountA(rng)%20%2B%2040%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22BE3881%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EActiveWindow.ScrollRow%20%3D%2041%3CBR%20%2F%3ERange(%22BE41%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteFormulas%2C%20Operation%3A%3DxlNone%2C%20_%3CBR%20%2F%3ESkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22BE41%3AB%22%20%26amp%3B%20lr)%2C%20Type%3A%3DxlFillDefault%3CBR%20%2F%3ERange(%22BE41%3AB%22%20%26amp%3B%20lr).Select%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlAutomatic%3CBR%20%2F%3ERange(%22B41%3AB%22%20%26amp%3B%20lr).Select%3CBR%20%2F%3ERange(%22B%22%20%26amp%3B%20lr).Activate%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22BE41%3AB%22%20%26amp%3B%20lr)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B41%3AB%22%20%26amp%3B%20lr)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlManual%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BG14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22BI5%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ECall%20CleanRSIcolumn%3C%2FP%3E%3CP%3EEnd%20With%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264034%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3BThank%20you%20Charla74%20for%20attempting%20to%20hep%20me.%20When%20I%20ran%20your%20code%20I%20see%20that%20instead%20of%20copying%20formula%26nbsp%3B%20be3881%20to%26nbsp%3B%3CSPAN%3EBE41%3ABE3043%2C%20it%20copied%20be3881%20to%20B41%3ABE3043%20overwriting%20all%20data%20in%20the%20table.%20I%20will%20compare%20code%20to%20see%20if%20I%20can%20find%20the%20problem.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264049%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264049%22%20slang%3D%22en-US%22%3E%3CP%3EOK%20I%20think%20I%20corrected%20it%2C%20however%20my%20whole%20macro%20takes%20almost%2030%20mins%20to%20complete%20so%20I%20must%20wait%20for%20result.%20Here%20is%20the%20change%20I%20made.%20Let%20me%20know%20if%20you%20see%20any%20trouble%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CopySortCalcRSI()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20CopyRSITemp%20Macro%3CBR%20%2F%3E'%3C%2FP%3E%3CP%3E'%3C%2FP%3E%3CP%3EDim%20rng%20As%20Range%3CBR%20%2F%3EDim%20lr%20As%20Integer%3CBR%20%2F%3ESet%20rng%20%3D%20Range(%22B41%3AB3880%22)%3CBR%20%2F%3Elr%20%3D%20WorksheetFunction.CountA(rng)%20%2B%2040%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22BE3881%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EActiveWindow.ScrollRow%20%3D%2041%3CBR%20%2F%3ERange(%22BE41%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteFormulas%2C%20Operation%3A%3DxlNone%2C%20_%3CBR%20%2F%3ESkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22BE41%3AB%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr)%2C%20Type%3A%3DxlFillDefault%3CBR%20%2F%3ERange(%22BE41%3AB%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr).Select%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlAutomatic%3CBR%20%2F%3ERange(%22B41%3AB%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr).Select%3CBR%20%2F%3ERange(%22B%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr).Activate%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22BE41%3AB%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B41%3AB%3CSTRONG%3EE%3C%2FSTRONG%3E%22%20%26amp%3B%20lr)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlManual%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BG14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22BI5%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ECall%20CleanRSIcolumn%3C%2FP%3E%3CP%3EEnd%20With%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264091%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264091%22%20slang%3D%22en-US%22%3EIt%20appears%20the%20corrections%20worked.%20Thank%20you%20so%20much%20for%20your%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264137%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20make%20Formula%20talk%20to%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApologies%20for%20my%20B%20vs%20BE%20oversight%20-%20glad%20you%20got%20it%20figured%20out!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

I have the following formula: =COUNTA($B41:$B3880)+40

And the following Macro:

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'
Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:BE3043"), Type:=xlFillDefault
Range("BE41:BE3043").Select
Application.Calculation = xlAutomatic
Range("B41:BF3043").Select
Range("BF3043").Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:BE3043"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BF3043")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

 

The formula tells me that I have 3043 populated cells in column B. The macro need to auto fill 3043 cell. I have been manually telling the macro how many lines to auto fill. How can I get these 2 code to talk?

7 Replies
Highlighted

@Greg Bonaparte 

 

I assume you have populated cells in column A41 and down from there and you want your formula in the equivalent number of cells in column B.......in that case, I would identify the last row from column A and use this for the range to enter your formula:

 

'Find the last used row in a Column: column A in this example

    Dim LastRow As Long

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    End With

 

You could then copy down a formula in B41 as follows:

 

    Range("B41:B" & LastRow).Filldown

 

Highlighted

Thank You Charla. Im a little confused however. I have data in row B therefor can not put formula in the cells. Second, Im not clear where I would plug in your code to mind. Would you kindly input your code. Do not consider row A. Thank you@Charla74 

Highlighted
Solution

@Greg Bonaparte 

 

Ok, seems I got the wrong end of the stick - try this, which incorporates your COUNTA in the code so no longer required in the worksheet:

 

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'

Dim rng As Range
Dim lr As Integer
Set rng = Range("B41:B3880")
lr = WorksheetFunction.CountA(rng) + 40


Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:B" & lr), Type:=xlFillDefault
Range("BE41:B" & lr).Select
Application.Calculation = xlAutomatic
Range("B41:B" & lr).Select
Range("B" & lr).Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:B" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:B" & lr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

Highlighted

@Charla74 Thank you Charla74 for attempting to hep me. When I ran your code I see that instead of copying formula  be3881 to BE41:BE3043, it copied be3881 to B41:BE3043 overwriting all data in the table. I will compare code to see if I can find the problem.

Highlighted

OK I think I corrected it, however my whole macro takes almost 30 mins to complete so I must wait for result. Here is the change I made. Let me know if you see any trouble

 

 

 

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'

Dim rng As Range
Dim lr As Integer
Set rng = Range("B41:B3880")
lr = WorksheetFunction.CountA(rng) + 40


Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:BE" & lr), Type:=xlFillDefault
Range("BE41:BE" & lr).Select
Application.Calculation = xlAutomatic
Range("B41:BE" & lr).Select
Range("BE" & lr).Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:BE" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BE" & lr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

@Greg Bonaparte 

Highlighted
It appears the corrections worked. Thank you so much for your help
Highlighted

@Greg Bonaparte 

 

Apologies for my B vs BE oversight - glad you got it figured out!