Consulting

Results 1 to 5 of 5

Thread: Need VBA Help With the following info

  1. #1
    VBAX Regular
    Joined
    Apr 2011
    Posts
    72
    Location

    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

  2. #2
    VBAX Regular
    Joined
    Apr 2011
    Posts
    72
    Location
    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 2nd macro will insert 14 columns starting in Column I in the new created sheet.
    3. The 3rd 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 3rd 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 3rd 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!
    Attached Files Attached Files

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try this

    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    Apr 2011
    Posts
    72
    Location
    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by rsrasc View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •