Macro to insert section from one workbook onto multiple sheets of another

[Nieriel]

Hi All,

I have a workbook which has one worksheet in it, this has a 'summary' section in it complete with formulas which needs to be pasted into another workbook which has 119 sheets, at the top of every sheet.
Basically I need a macro to insert 30 blank rows onto all 119 sheets (or to ensure it moves all the data from columns A-L down 30 lines) then insert this 'summary' section without losing any of the formulas as these then calculate totals based on the data that has moved down.
I have tried to record one but it will not let me paste onto multiple sheets so would anyone be able to help me with a macro for this.
I don't mind if it links to the file with the summary on as this will be stored in a static location.
Anyone have any ideas if this is possible or am I asking too much of excel?
Regards

 

[JLGWhiz]

It would have been helpful to have the workbook names, but you can add those in where the code uses index numbers. See comments in code. This code assumes both workbooks are open at runtime.

Sub copyMultSh()
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks(2)
    For Each sh In wb.Sheets
        sh.Range("A1:L30").Insert xlShiftDown
        ThisWorkbook.Sheets("summary").Range("A1:L30").Copy sh.Range("A1")
    Next
End Sub

 

[Nieriel]

Thank you for the macro - I think I am typing my workbook names in the wrong place as at the moment I can get it that the macro inserts 30 lines into the summary book not the workbook with all the sheets on it.
I am trying to insert 30 lines and the summary onto a workbook called "Oct Export3", the workbook I am trying to copy it from is called "Income Summary"
Am I just typing the names in wrong - I have tried several different ways but none seem to come up and I have both workbooks open at the same time.
Regards

 

[JLGWhiz]

Thank you for the macro - I think I am typing my workbook names in the wrong place as at the moment I can get it that the macro inserts 30 lines into the summary book not the workbook with all the sheets on it.
I am trying to insert 30 lines and the summary onto a workbook called "Oct Export3", the workbook I am trying to copy it from is called "Income Summary"
Am I just typing the names in wrong - I have tried several different ways but none seem to come up and I have both workbooks open at the same time.
Regards

The code is written on the assumption that the it will be copied to the standard code module 1 of the workbook ("Income Summary")containing the sheet "summary", with a small 's'. The only workbook requiring a name tweak is the one with the 119 sheets, designated 'Workbooks(2)' in the code. Just remove the 2 in the parentheses and replace it with the actual workbook name and file extension, enclosed in quotation marks, eg:Workbooks("Oct Export3.xlsx"). Those are the only two things you really need to do. The code should then insert space in the sheets of Oct Export3 and copy over the data from sheet summary from the other workbook.

If you cannot yet read macros to the extent that you understand how they apply to the workbooks and worksheets, then you should make that clear in your posts so that the responder can include some guidelines for use along with the code.

 

[Nieriel]

Thank you very much for your help - it was me missing the fact that my summary tab had a capital "s" doh! I am relatively new to macros so appreciate all the help - I'll ensure that I include in my future posts that I am a newbie to VBA
Thanks again

 

[JLGWhiz]

Thank you very much for your help - it was me missing the fact that my summary tab had a capital "s" doh! I am relatively new to macros so appreciate all the help - I'll ensure that I include in my future posts that I am a newbie to VBA
Thanks again

Happy to help,
Regards, JLG