Link to home
Create AccountLog in
Avatar of Ray Erden
Ray ErdenFlag for United States of America

asked on

Copy formulas from defined ranges on a static tab to the same ranges on any other active worksheet in Excel via VBA.

On the attached Excel file I am tracking my workouts, on Module 7 of VBA editor I have the code where I have a question about.  I am trying to have this code to copy formulas in defined ranges from "April 3 to April 9" worksheet to the target worksheet named as "April 10 to April 16" in fact the goal is to copy/paste those formulas from the same tab to any other active worksheet. 

There are 6 source and destination ranges and they are identical on source/target tabs. When I run the code it only copies tha last range which is the AK3:AK9.  If I add more ranges it will copy and paste the last range whatever it may be and I don't get why ot does that. So I commented out all source/dest ranges displayed starting with Set command except the Q3:Q9 range for no specific reason. When I run the code it copies from Q3:Q9 of source to target in its current state.

I have come up with a rough alternate solution, I can get the modified code to achieve via a macro call at the bottom of the code and this called recorded macro is residing on Module 8. All it does it to copy and paste after the formulas were brought over to the Q3:Q9 rabge on target from the source tab. Why is it not coying and pasting all the ranges when I run it but it is doing it only for one range is my predicament.

Avatar of Ray Erden
Ray Erden
Flag of United States of America image


Here is the file in question attached to this comment, thank you again.
VBA code is on the file attached in the comment and it already does the job somewhat partially yet I need some improvements to get it work in the way I wanted.

It's been more than a day since I posted this question yet no one has attempted to help yet.
I don't think the way I phrased the question is confusing or misleading, certainly not hard for the experts to take a stab at.

Help will be appreciated.
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

better would be 

Sub CopyFormulas2()

    Dim wsSource As Worksheet ' Source worksheet

    Dim wsDestination As Worksheet ' Destination worksheet


    ' Set source worksheet

    Set wsSource = ThisWorkbook.Sheets("April 3 to April 9")

        ' Set destination worksheet (use the active sheet as the destination)

    Set wsDestination = ActiveSheet


    CopyRangeFormula "L3:L9", wsSource, wsDestination

    CopyRangeFormula "Q3:Q9", wsSource, wsDestination

    CopyRangeFormula "V3:V9", wsSource, wsDestination

    CopyRangeFormula "AA3:AA9", wsSource, wsDestination

    CopyRangeFormula "AF3:AF9", wsSource, wsDestination

    CopyRangeFormula "AK3:AK9", wsSource, wsDestination


End Sub

Sub CopyRangeFormula(rngText As String, wsSrc As Worksheet, wsDestin As Worksheet)


    wsDestin.Range(rngText).Formula = wsSrc.Range(rngText).Formula

End Sub

Thank you robberbarron!