Ask a question Report

Excel Macro: Copy formulas across worksheets [Solved]

Bobo - Latest answer on Jun 11, 2010 06:37PM
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
Read more 
moins plus
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 06:37PM
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!
Add comment
moins plus
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 11:45AM
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 4299Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Jun 11, 2010 12:22PM
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 01:04PM
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 4299Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Jun 11, 2010 01:07PM
"Data_Reciever.xlsx would have 30 sheets in all ? so basically copy data to all sheets of ""Data_Reciever.xlsx" ?
Bobo- Jun 11, 2010 01:10PM
Yup! From "Copy_Box.xlsx"
Add comment
This document entitled « Excel Macro: Copy formulas across worksheets » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.