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

Carlitotollito

New Member
Joined
Jul 9, 2018
Messages
1
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:

Code:
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.

Code:
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.
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top