VBA to copy from one workbook into another
 

Hi I am hoping I might be able to get a bit of help starting my first big (big to me!) VBA project. I will do my best to explain

a)I have an archive workbook with a series of indexed exercise programmes.
Each exercise programme in this workbook is indexed on a separate tab as A1, A2, A3 and so on
b) I have a separate workbook that is a ‘live’ training programme for an athlete
c) Each tab in the 'live' training programme indicates a numbered training week (1-52).
d) The front page of this work book is a dashboard view of the 52 week training plan. A mock up of this is below. weeks 1-52 along the top.
e) I want to use the front page ‘dashboard’ to write in a exercise programme code which corresponds to one of the archive workbook tabs. I want the VBA to then locate the correct indexed programme from the archive workbook, copy the programme and then paste it into the appropriate tab (week 1-52) in the ‘live’ workbook.
f) I am really new to VBA and have been trying to work this through but failing miserably!!! In my head it should be a simple LOOKUP, copy a set range of cells and then paste into a set range of cells as defined by the dashboard


Archive range Destination range WEEK
(Copy) (Paste) 1 2 ...... 52

Aerobic 1 D27:AD27 D27:AD27 A1 A2

Aerobic 2 D27:AD27 D29:AD29 A3 A4

Aerobic 3 D27:AD27 D31:AD31 A5 A6

Aerobic 4 D27:AD27 D33:AD33 A7 A7

Strength 1 B47:AD56 B47:AD56 S2 S3

Strength 2 B47:AD56 B62:AD71 S6 S7

Strength 3 B47:AD56 B77:AD86 S1 S1

Strength 4 B47:AD56 B92:AD101



Any assistance or pointers will be hugely appreciated

Thanks all

Paul