Excel Macro: Copy formulas across worksheetsSolved/Closed

Question

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



Configuration: Windows XP / Firefox 3.6.3

rizvisa1 · Accepted Answer

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

rizvisa1 · Answer

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

Excel Macro: Copy formulas across worksheets

2 responses

Similar discussions

Subscribe To Our Newsletter!