Excel Macro: Copy formulas across worksheets [Solved/Closed]

Ask a question Bobo - Last answered on Sep 24, 2017 at 07:56 AM by OCG
So first off, I have no Idea how to program, but I can kind of understand simple programs.

Either way, I'm trying to figure out a macro in MS Excel. This Macro would allow me to paste a group of cells of text and formulas onto a select range across every worksheet in a workbook. This could be pasted from information in the macro or pasted from another workbook.


in workbook1 I has a 3 formulas that add various boxes together

in work book2 I Want the 3 formulas pasted across 10 worksheets all in the same place on each worksheet.

Any help would be nice

plus moins
Ok try this

This macro needs to goto to the destination workbook. Destination workbook will open the source workbook. You need to correct the path of the source workbook. The macro will copy the data in P1:W15 from the source worbook to every single worksheet in destination workbook

1. Open the destination workbook (Data_Reciever.xlsx)
2. Press ALT + F11 to launch VBE
3. Click on Insert and add a new module
4. copy and paste the code below
5. To run the code, press F5

Sub ImportData()
Dim WBsrc As Workbook
Dim WBdes As Workbook
Dim WSsrc As Worksheet
Dim WSdes As Worksheet

    Set WBdes = ThisWorkbook
    Set WBsrc = Workbooks.Open("C:\Users\shamikh\Downloads\Counting Box.xlsx")
    Set WSsrc = WBsrc.Sheets("Sheet1")
    Set WSdes = ActiveSheet
    Application.CutCopyMode = xlCopy
    For Each Sheet In Sheets
        If Sheet.Name = WSdes.Name Then GoTo Next_Sheet
    Next Sheet
    Set WSsrc = Nothing
    Set WSdes = Nothing
    Set WBsrc = Nothing
    Set WBdes = Nothing
End Sub
Bobo- Jun 11, 2010 at 06:37 PM
I only got a few minutes to play with it before I had to leave the office for the week-end (I don't have excel at home), but from what I could tell it worked like a charm!

I'll tell you if any problems come up.

thanks for all your help!
OCG- Sep 24, 2017 at 07:56 AM
wow you saved my file!!
i had this bug in a huge file with formulas and stuff and i didnt want to just copy-paste values. this did the work
plus moins
Could you please upload a sample file with sample data etc on some shared site like ,, , etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Bobo- Jun 11, 2010 at 11:45 AM
the answer to 1 would be no.

I'm guessing any macro would be run from the Excel file being pasted to, be directed to the "Counting_Box.xlsx" file, select the range, copy, comeback to the file and paste across the worksheets.
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Jun 11, 2010 at 12:22 PM
Now I am at total loss. Now it seems that you would be running a macro from another book, and you want to copy the data to this
Bobo- Jun 11, 2010 at 01:04 PM
I'll just review step by step what I want the macro to do.

first we have "copy_Box.xlsx" which has the data
second we have hypothetical file Data_Reciver.xlsx
Data_Reciver.xlsx has 30 worksheets within the workbook.

when I start the macro I want it to:
1. find "Copy_Box.xlsx"
2. select P1:W15
3. copy P1:W15
4. find "Data_Reciver.xlsx"
5. select the first worksheet
6. select M2:T16
7. paste
8. repeat step 5-7 for worksheet 2-30
9. end

"Copy_Box.xlsx" is unchanged and "Data_Reciever.xlsx" now has all of "Copy_Box.xlsx"s information across all of it's worksheets.

i don't care which file is running the macro. it just seems easier for "Data_Reciever.xlsx" to run it since most of the action is in that file, but that is just from an unknowledgeable observer.

thanks for your persistence :)
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Jun 11, 2010 at 01:07 PM
"Data_Reciever.xlsx would have 30 sheets in all ? so basically copy data to all sheets of ""Data_Reciever.xlsx" ?
Bobo- Jun 11, 2010 at 01:10 PM
Yup! From "Copy_Box.xlsx"

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!