Macro Loop

S

stan

Hello,

Could someone help me turn the following into a loop?

Thanks!

Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BC7").Select
ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
Range("BC7").Select
Selection.AutoFill Destination:=Range("BC7:BC500")
Calculate
Columns("BC:BC").Select
Range("BC7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AU:BB").Select
Range("BB1").Activate
Selection.Delete Shift:=xlToLeft
Range("AU4").Select
ActiveCell.FormulaR1C1 = "Portfolio Manager "

Columns("BD:BD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BD7").Select
ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
Range("BD7").Select
Selection.AutoFill Destination:=Range("BD7:BD500")
Calculate
Columns("BD:BD").Select
Range("BD7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AV:BC").Select
Range("BC1").Activate
Selection.Delete Shift:=xlToLeft
Range("AV4").Select
ActiveCell.FormulaR1C1 = "Portfolio Manager "

Columns("BE:BE").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BE7").Select
ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
Range("BE7").Select
Selection.AutoFill Destination:=Range("BE7:BE500")
Calculate
Columns("BE:BE").Select
Range("BE7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AW:BD").Select
Range("BD1").Activate
Selection.Delete Shift:=xlToLeft
Range("AW4").Select
ActiveCell.FormulaR1C1 = "Portfolio Manager "

Columns("BF:BF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BF7").Select
ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
Range("BF7").Select
Selection.AutoFill Destination:=Range("BF7:BF500")
Calculate
Columns("BF:BF").Select
Range("BF7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AX:BE").Select
Range("BE1").Activate
Selection.Delete Shift:=xlToLeft
Range("AX4").Select
ActiveCell.FormulaR1C1 = "Portfolio Manager "

Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BG7").Select
ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
Range("BG7").Select
Selection.AutoFill Destination:=Range("BG7:BG500")
Calculate
Columns("BG:BG").Select
Range("BG7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AY:BF").Select
Range("BF1").Activate
Selection.Delete Shift:=xlToLeft
Range("AY4").Select
ActiveCell.FormulaR1C1 = "Portfolio Manager "
 
M

muddan madhu

Below code is say you are deleting Au:BB and again you are inputing
formula as
"=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"

which is not required....

Columns("AU:BB").Select
Range("BB1").Activate
Selection.Delete Shift:=xlToLeft




Hello,

Could someone help me turn the following into a loop?

Thanks!

Columns("BC:BC").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("BC7").Select
    ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
    Range("BC7").Select
    Selection.AutoFill Destination:=Range("BC7:BC500")
    Calculate
    Columns("BC:BC").Select
    Range("BC7").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AU:BB").Select
    Range("BB1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("AU4").Select
    ActiveCell.FormulaR1C1 = "Portfolio Manager "

   Columns("BD:BD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("BD7").Select
    ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
    Range("BD7").Select
    Selection.AutoFill Destination:=Range("BD7:BD500")
    Calculate
    Columns("BD:BD").Select
    Range("BD7").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AV:BC").Select
    Range("BC1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("AV4").Select
    ActiveCell.FormulaR1C1 = "Portfolio Manager "

    Columns("BE:BE").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("BE7").Select
    ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
    Range("BE7").Select
    Selection.AutoFill Destination:=Range("BE7:BE500")
    Calculate
    Columns("BE:BE").Select
    Range("BE7").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AW:BD").Select
    Range("BD1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("AW4").Select
    ActiveCell.FormulaR1C1 = "Portfolio Manager "

    Columns("BF:BF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("BF7").Select
    ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
    Range("BF7").Select
    Selection.AutoFill Destination:=Range("BF7:BF500")
    Calculate
    Columns("BF:BF").Select
    Range("BF7").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AX:BE").Select
    Range("BE1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("AX4").Select
    ActiveCell.FormulaR1C1 = "Portfolio Manager "

    Columns("BG:BG").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("BG7").Select
    ActiveCell.FormulaR1C1 = "=(-SUM(RC[-8]:RC[-5])+SUM(RC[-4]:RC[-1]))"
    Range("BG7").Select
    Selection.AutoFill Destination:=Range("BG7:BG500")
    Calculate
    Columns("BG:BG").Select
    Range("BG7").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AY:BF").Select
    Range("BF1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("AY4").Select
    ActiveCell.FormulaR1C1 = "Portfolio Manager "
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hard reference in a macro 1
moving all data to cell a1 4
Complie Syntax Error 6
Macro should Save and Overwrite file 1
Replace a fixed number with a variable via popup 3
Profress Indicator 2
Macro Flexability 7
excel macro missing worksheets 9

Top