Macro for sending data to another spreadsheet

[traff1982]

Hi everyone

I'm pretty good on formulas but am new to using macro's within Excel.

I have a speadsheet (called "INPUT") which calculates a number of outputs for certain members when details are entered.

I also have a separate spreadsheet (called "DATA") that will hold the data from the first spreadsheet. It has the following headings, next to which, i have put the cell reference of the corresponding data to be put in from "INPUT".

FP - "D34"
TFCS - "J33"
CFact - "L1"
LTA1 - "F33"

As there are 400 different members it would be very time consuming to input all the data onto the 2nd spreadsheet. Is there any way of having a click button (rectangle click?) which would send the answers to the 2nd spreadsheet to the corresponding titles?

Any help would be really appreciated!

Many thanks!

Marc

 

[ravishankar]

HI
if you have 4 cells in input and 400 names, Do you want only added names to be copied to sheet2 or all 400 names in one go?
Ravi

 

[traff1982]

The "INPUT" spreadsheet can only handle one member at a time so the "DATA" sheet would need to be updated each time a calc was completed, eventually showing 400 on the "DATA" sheet.

Cheers

 

[ravishankar]

HI
Paste the following codes in the macro window ( Alt F11)

Sub Transfer()
x = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To 4
b = Choose(a, 34, 33, 1, 33)
c = Choose(a, 4, 10, 12, 6)
Worksheets("Data").Cells(x + 1, a + 1) = Worksheets("Input").Cells(b, c)
Next a
MsgBox "Data transfer completed"
End Sub

run the macro after entering data in input sheet.
Ravi

 

[traff1982]

Thanks

That is transferring the data but is replacing the data previously held rather than updating the sheet.

 

[ravishankar]

Hi
I think we are not clear of the layout of your sheets. It is unlikely, it can overwrite the data since the code searches for last row in col A of "DATA" sheet and pastes values in the next row. What am I missing?
Ravi