Tricky copy and paste macro

Need a macro that will copy cut and paste with (cut mode of and screen updating off) the data from B5:BA95 (worksheet Pricing Summary).

If the list grows beyond row 95 then allow for the increase in new supply customer details when copying and pasting.. to the demand table (see row 102 and beyond)

Less:
The Tricky Part - I do not wish to copy any part of column E for the Supply Table ..as these named ranges will be different to the demand table part


Copy and paste this information into the Demand Table minus Column E.

Point to note that the position of the Demand table will move as customer are added to the bottom of the supply table list or as customer are deleted.


Via the Assign Macro button

Something like

Code:

---


Sub DoCopy()
    Dim Source As Range, Tgt As Range
    Set Source = Range("B5")
    Set Tgt = Columns(2).Find("Demand").Offset(2)
    Range(Source, Source.End(xlDown)).Resize(, 3).Copy Tgt
    Range(Source, Source.End(xlDown)).Offset(, 4).Resize(, 48).Copy Tgt.Offset(, 4)
End Sub

---

Is the code suppose to be for worksheet "Pricing Summary"

Yes

Apologies......cannot get it to work..........Must be having a terrible day....

And this is not a joke....Once again Apologies

Code revised to add sheet reference. The copy seems to be working, but I'm getting a link to another workbook popping up. Is that what you mean?

Code:

---


Sub DoCopy()
    Dim Source As Range, Tgt As Range
    With Sheets("Pricing Summary")
        Set Source = .Range("B5")
        Set Tgt = .Columns(2).Find("Demand").Offset(2)
    End With
    Range(Source, Source.End(xlDown)).Resize(, 3).Copy Tgt
    Range(Source, Source.End(xlDown)).Offset(, 4).Resize(, 48).Copy Tgt.Offset(, 4)
End Sub

---

thanks it works - fine