Macro for copying data from fixed cells, but pasting to different cells each time

[Carlitotollito]

Hi,

I am currently in the process of converting all of our files at work from Lotus 123 to Excel 2013.

I need to create a macro which copies data from a number of different cells (which are always the same, i.e. the plot number will always need to be copied from sheet 'Plot Input' cell C2), and then paste to an area for keeping records of the specific data.

The problem being that for plot 1 for example I would need the data from sheet 'Plot Input' cell C2 to be pasted to sheet 'Job Setup' cell P2. But then when I run plot 2 I would need data from sheet 'Plot Input' cell C2 to be pasted to sheet 'Job Setup' cell X2, AF2 on plot 3, AN2 on plot 4 etc.

What would be the easiest way to do this, without individually editing the macro for each plot. As there are some 300 spaces for keeping records and I'd rather not have to edit the macro that many times.

Below is the script from the 'Copy Button' within Lotus 123:

Sub Click(Source As Buttoncontrol)
    [Plot Input:C2].Select  
    Selection.CopyToClipboard 
    [Job Setup:P2].Select     
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C4].Select  
    Selection.CopyToClipboard 
    [Job Setup:P4].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:F2].Select  
    Selection.CopyToClipboard 
    [Job Setup:S2].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:F4].Select  
    Selection.CopyToClipboard 
    [Job Setup:S4].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C6].Select  
    Selection.CopyToClipboard 
    [Job Setup:P6].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C12..Plot Input:F12].Select  
    Selection.CopyToClipboard 
    [Job Setup:P8].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,    
    [Plot Input:C17..Plot Input:F17].Select  
    Selection.CopyToClipboard 
    [Job Setup:P13].Select      
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C14..Plot Input:F14].Select  
    Selection.CopyToClipboard 
    [Job Setup:P10].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C16..Plot Input:F16].Select  
    Selection.CopyToClipboard 
    [Job Setup:P12].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,    
    [Plot Input:C52..Plot Input:F52].Select  
    Selection.CopyToClipboard 
    [Job Setup:P14].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C72].Select  
    Selection.CopyToClipboard 
    [Job Setup:P17].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C8].Select  
    Selection.CopyToClipboard 
    [Job Setup:P18].Select 
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C108..Plot Input:F108].Select  
    Selection.CopyToClipboard 
    [Job Setup:P19].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C110..Plot Input:F110].Select  
    Selection.CopyToClipboard 
    [Job Setup:P21].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C116].Select  
    Selection.CopyToClipboard 
    [Job Setup:P23].Select     
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C40].Select  
    Selection.CopyToClipboard 
    [Job Setup:P25].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:I2].Select  
    Selection.CopyToClipboard 
    [Job Setup:Q27].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:I3].Select  
    Selection.CopyToClipboard 
    [Job Setup:S27].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Job Setup:N2].Select  
End Sub

And this is the script for the copy button for the next set of records. As you can see, all that changes is the location of where the copied data is pasted. Surely you don't have to go through and edit these for each one. There must be a better way.

Sub Click(Source As Buttoncontrol)
    [Plot Input:C2].Select  
    Selection.CopyToClipboard 
    [Job Setup:X2].Select     
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C4].Select  
    Selection.CopyToClipboard 
    [Job Setup:X4].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:F2].Select  
    Selection.CopyToClipboard 
    [Job Setup:AA2].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:F4].Select  
    Selection.CopyToClipboard 
    [Job Setup:AA4].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C6].Select  
    Selection.CopyToClipboard 
    [Job Setup:X6].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C12..Plot Input:F12].Select  
    Selection.CopyToClipboard 
    [Job Setup:X8].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C14..Plot Input:F14].Select  
    Selection.CopyToClipboard 
    [Job Setup:X10].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C16..Plot Input:F16].Select  
    Selection.CopyToClipboard 
    [Job Setup:X12].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,    
    [Plot Input:C17..Plot Input:F17].Select  
    Selection.CopyToClipboard 
    [Job Setup:X13].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C52..Plot Input:F52].Select  
    Selection.CopyToClipboard 
    [Job Setup:X14].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C72].Select  
    Selection.CopyToClipboard 
    [Job Setup:X17].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C8].Select  
    Selection.CopyToClipboard 
    [Job Setup:X18].Select 
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C108..Plot Input:F108].Select  
    Selection.CopyToClipboard 
    [Job Setup:X19].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C110..Plot Input:F110].Select  
    Selection.CopyToClipboard 
    [Job Setup:X21].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C116].Select  
    Selection.CopyToClipboard 
    [Job Setup:X23].Select     
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:C40].Select  
    Selection.CopyToClipboard 
    [Job Setup:X25].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:I2].Select  
    Selection.CopyToClipboard 
    [Job Setup:Y27].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Plot Input:I3].Select  
    Selection.CopyToClipboard 
    [Job Setup:AA27].Select  
    Selection.Paste $NativeFormat,False,PasteData + PasteFormulas,,,,
    [Job Setup:N2].Select  
End Sub

Thanks in advance for any help and apologies if this is something that has already been discussed/resolved, this is all quite new to me.

Carl.