Solved: Tricky copy and paste macro

[Pete]

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

[mdmackillop]

Something like
[vba]
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

[/vba]

[Pete]

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

[mdmackillop]

Yes

[Pete]

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

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

[mdmackillop]

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?

[VBA]
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

[/VBA]

[Pete]

thanks it works - fine