Copy multiple cells to another worksheet

spraggn

New Member
Joined
May 15, 2005
Messages
8
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Nick

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


Tony
 

spraggn

New Member
Joined
May 15, 2005
Messages
8
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Nick

Try
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,021
Messages
5,834,992
Members
430,331
Latest member
Syed Yasir Hannan

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
Top