Excel Macro: Copy formulas across worksheets

Solved/Closed
-
 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.

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



2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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!
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , 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
Thanks rizvisa for your speedy response!

I uploaded the file to speedyshare at:
https://authentification.site/files/22908968/Counting_Box.xlsx

I want the range of P1:W15 (in the uploaded file) to be pasted to the Range of M2:T16 in a different Excel file. The excel files that I will paste the range into each have around 30 worksheets each that each need the pasted cells.

I will never be changing the range for either Excel file.

If you need anymore clarification or help from me, just ask.

thanks
oh, one more thing, the files will always be in the same folder.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
When you say that you want to paste on different, clarify few things
1. Are those sheets where data would be pasted in the same book from where the data is being copied

2. If answer to #1 is no, then clarify how one would know whats books needs to be accessed to paste the data

3. If answer to #1 is yes, the clarify that does it mean that copy on all sheets in that workbook except this one ? or only paste on few selected worksheet.
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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