Copying formulas between workbooks

[RichardMGreen]

Hi all

I need to copy a load of formulas between workbook without copying the sheets themselves.
All the formulas will be in the same cells (A1 to A1) and the sheet names are identical.
I've got this piece of code :-

Sub copier()
    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet, rng As Range
    Set wb1 = Workbooks("NOCREP0036 - TEMPLATE 111 Report Dashboard updating version v1.33.xls")
    Set wb2 = ThisWorkbook
    For Each ws In wb1.Worksheets
        sheet_name = ws.Name
        wb1.Sheets(sheet_name).Select
        For Each rng In ActiveSheet.UsedRange
            wb2.Sheets(sheet_name).rng.Formula = ActiveSheet.rng.Formula
        Next
    Next
End Sub

but it's falling over on this line :-
wb2.Sheets(sheet_name).rng.Formula = ActiveSheet.rng.Formula

Obviously I'm doing something wrong but I can't see it.

Can anyone point me in the right direction?

 

[RSXchin]

untested, but maybe

Sub copier()

    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet, rng As Range
    Set wb1 = Workbooks("NOCREP0036 - TEMPLATE 111 Report Dashboard updating version v1.33.xls")
    Set wb2 = ThisWorkbook
    For Each ws In wb1.Worksheets
        sheet_name = ws.Name
        wb1.Sheets(sheet_name).copy
        wb2.Sheets(sheet_name).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Next
    Next

End Sub</pre>

 

[RichardMGreen]

Thanks for that.
Solved it in the end.

 

[RSXchin]

did my code work? if not, please post your code that made it work