Copy multiple cells to another worksheet

[spraggn]

Hi, i recorded a macro to copy data in one worksheet (Input File) into another (Calculation).

The way that I did it was one cell at a time so for instance cell D2 in the 'Input File' worksheet was copied to cell D4 in the 'Calculation' worksheet.

This is repeated across a number of cells in the 'Input File' worksheet.

Can someone please help with the code to copy all the required cells in one hit (ie for all the cells required in the 'Input File' worksheet rather than going backwards and forwards between the sheets) and them paste them to the appropriate cells in the 'Calculation' worksheet in one hit.

Below is a subset of the code that I have (in this example there are 3 cells to copy but I will actually require to copy about 10 cells in one go).



Sub IFRS_Calculations()

‘ IFRS_Calculations Macro
‘ Macro recorded 10/11/2005 by spraggn1



Sheets("Input File").Select
Range("D2").Select
Range("D2").Copy
Sheets("Calculation").Select
Range("D4").Select
ActiveSheet.Paste

Sheets("Input File").Select
Range("F2").Select
Range("F2").Copy
Sheets("Calculation").Select
Range("D8").Select
ActiveSheet.Paste

Sheets("Input File").Select
Range("J2").Select
Range("J2").Copy
Sheets("Calculation").Select
Range("D10").Select
ActiveSheet.Paste

End Sub

Eg the spreadsheets have the following data (I have only included the 3 cells in the code above but there is data in A1:AF1 in the 'Input File' worksheet and I need to copy about 10 of them, which are not all adjacent)

'Input File' worksheet
D2 F2 J2
AASB139 1 0.007791666666667

'Calculation' worksheet

D4 (Accounting standard) - InputFile!D2 to be pasted
D8 (Balance) - InputFile!F2 to be pasted
D10 (Discount Rate) - InputFile!J2 to be pasted

Please note the mappings of the cells to copy from in the 'Input File' and then paste to in the 'Calculations' worksheet are vital to the operation


Any help would be greatly appreciated

Thanks

Nick

 

[acw]

Nick

Can you please give a listing of all the input and output cells required and the mappings between the cells.


Tony

 

[spraggn]

Hi Tony

The mappings are as follows

Input File - Calculations

D2 - D4
F2 - D8
J2 - D10
N2 - D11
AE2 - D12
X2 - D13
Y2 - D14
G2 - D15
Q2 - D16
K2 - D21
O2 - D22
H2 - D23
P2 - D24
R2 - D25
I2 - D30

Hope this helps

Thanks

Nick

 

[acw]

Nick

Try

Sub aaa()
 Iarr = Array("D2", "F2", "J2", "N2", "AE2", "X2", "Y2", "G2", "Q2", "K2", "O2", "H2", "P2", "R2", "I2")
 Oarr = Array("D4", "D8", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D21", "D22", "D23", "D24", "D25", "D30")
 For i = LBound(Iarr) To UBound(Iarr)
  Sheets("Calculation").Range(Oarr(i)).Value = Sheets("Input File").Range(Iarr(i)).Value
 Next i
End Sub

Tony