Need VBA Help With the following info

Hi all,

I have about 25 workbooks that I would like some help. In every workbook, i have a worksheet named "FY21-Budget Variance".

In all of the worksheets, in Column F, from rows F6 thru F250, I have the word "Budget". Depending on how many accounts i have i could have the word "Budget" repeated at least 25 times or more--sometime less.


As an example, in row F20, I have the word "Budget". Then in Column H, I have the following formula in cell H20, "=SUM(H15:H19)".

With a macro I would like to copy the range from H20 all the way thru Column AD20 for every row where the word Budget is shown in Column F.

Any help is much appreciated.

I hope this is clear. If not, please let me know.

Regards,
rsrasc

Hi all,

The following is an update to my most recent post.

Since I have made some progress on what I’m trying to do, I’m attaching a working copy of the file.

The file has two sheets on it.

The first one is named “FY 21-Budget-Expenses-Option2”, and the second one is named “FY21-Monthly Variance Report-T”, which is the sheet generated after running the macros located under Module 1. I added here as a sample just to show you the final results after running the macros.

The macros in Module 1 will do the following:

1. It will create a copy of the sheet “FY 21-Budget-Expenses-Option2”, and it will name it ““FY21-Monthly Variance Report”.
2. The 2^nd macro will insert 14 columns starting in Column I in the new created sheet.
3. The 3^rd macro will look for any value in Column F for the word “Budget” in the new created sheet and it will copy the formula that is in the same row starting in Column H, and will copy the formula from Column H all the way through Column AE.

Therefore, the 3^rd macro is where I need some help.

For example, if you look at cell H162 you will see the following formula:

=SUM(H159:H161)

If you look at cell I162, you will see the following formula:

=SUM(H159:H161)

As you can see, when running the 3^rd macro to copy the formulas from Column H thru Column AE, and as an example, the formula for cell I162 is the following:

=SUM(H159:H161) and should be =SUM(I159:I161)

So if I’m explaining this correctly, the cell reference in the formula in every column should make reference to that same column letter.

If you look at Column F and where ever you see the word “Budget” next to this word and starting in Column H all the way thru Column AE, the same formula is repeated.

Like I said in my post, it will be great if someone can help me with this since I have over 25 files that I would need to do the same.

Finally, thank you in advance for your assistance and cooperation.

Any questions, please let me know.

Regards!

Try this

Code:

---

Sub CopyCells____1111()
Dim rng As Range
Dim lastrow As Long
Dim i As Long

    On Error GoTo sub_exit
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set rng = Range("H2:AE2" & lastrow)
    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
   
    For i = 1 To lastrow
   
        If Cells(i, "F").Value = "Budget" Then
           
            Cells(i, "H").Copy
            Cells(i, "I").Resize(, 25).PasteSpecial Paste:=xlPasteFormulas
        End If
    Next

sub_exit:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

---

Hello Mr. Phillips,

I love how you describe yourself---"Distinguished Lord of VBAX" and also "VBAX Grand Master", and if fact you are.

I tested your code and its working great. Honestly, I was losing my faith but you came along and make my morning.

Thank you for your assistance and cooperation.

Much appreciated.

rsrasc

Quote:

---

Originally Posted by rsrasc

Hello Mr. Phillips,

I love how you describe yourself---"Distinguished Lord of VBAX" and also "VBAX Grand Master", and if fact you are.

---

Thank-you for those sentiments, but I should add that it is not something that I describe myself as. Every member has a rank depending upon their experience and their posting history, for instance you are a VBA Newbie as you are new here and don't have many posts. That rank was created for me by the board of this forum when I became the first person to reach a particular milestone number of posts.