Copying cells and formula from closed workbook to all sheets in open workbook

[fishthefishyfish]

Hiya

As you can see I am a newbie to this forum but have been trying to find a solution to my problem but nothing seems to work.

I am using excel 2010 and have a sheet (named Open) in a workbook (Conductivity calculator.xlsx) that contains a large range of cells (D3:Z553) with both formula and data that I need to be able to copy across to a different range (E1:AA551) in all of the open worksheets (various already named sheets) in a new workbook that is already open from another macro function (it seems to have the name sheet1 for some reason).

The macro I am using is as follows:

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( [Source File extension here]\[source file name here.xlsx")

'wbsrc.
Set WSsrc = WBsrc.Sheets("Open")

WSsrc.Select
Range("D3:Z553").Copy

WBdes.Activate
Set WSdes = ActiveSheet

Range("E1:AA551").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("E1:AA551").Copy

Sheet.Select
Range("E1:AA551").PasteSpecial

Next_Sheet:
Next Sheet

Set WSsrc = Nothing
Set WSdes = Nothing

Set WBsrc = Nothing
Set WBdes = Nothing

End Sub
​

It succefully opens the source workbook but just leaves the destination worksheets unchanged.

Is anybody able to see my error? I have had to remove the links as it is my first post!

Thanks