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.
EX:
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 
Answer
+4
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


Steps:
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")
    
    'wbsrc.
    Set WSsrc = WBsrc.Sheets("Sheet1")
    
    WSsrc.Select
    Range("P1:W15").Copy
    
    WBdes.Activate
    Set WSdes = ActiveSheet
    
    Range("M2:T16").PasteSpecial
    Application.CutCopyMode = xlCopy
    WBsrc.Close
    
    WBdes.Activate
    For Each Sheet In Sheets
        If Sheet.Name = WSdes.Name Then GoTo Next_Sheet
        
        WSdes.Select
        Range("M2:T16").Copy
        
        Sheet.Select
        Range("M2:T16").PasteSpecial
        
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!
Reply
Add comment
Answer
+0
moins plus
Could you please upload a sample file with sample data etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com 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.
Reply
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
Reply
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 :)
Reply
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" ?
Reply
Bobo- Jun 11, 2010 01:10PM
Yup! From "Copy_Box.xlsx"
Reply
Add comment
This document entitled « Excel Macro: Copy formulas across worksheets » from CCM (ccm.net) 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.