Macro for sending data to another spreadsheet

traff1982

Board Regular
Joined
May 21, 2007
Messages
62
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

Board Regular
Joined
May 21, 2007
Messages
62
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
HI
Paste the following codes in the macro window ( Alt F11)

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

Board Regular
Joined
May 21, 2007
Messages
62
Thanks

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

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

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