I have a spread sheet with two worksheets. One is Input Data the other is
Data. The first sheet inputs mileage, LPG gas in litres, cost of gas in
pence, comparison cost of petrol in pence, Any miles done on petrol. Plus A
cell as a button to input the data to the data sheet. I want to move the
data from these fixed cells and put it into the first free cell at the bottom
of the relevant column on the data sheet when I click on the input data
button. The data sheet will then do all of the calculation and will send the
averaged fuel consumption for the input data and the averaged fuel
consumption for ALL input data back to the relevant cells on the input data
sheet. I do not need to see the data sheet at all unless I click on the tab
at the bottom of the screen.

Thanks in advance

--
daedalus1

I'm going into overkill mode here. The code shown below will take the
information from the last used row on the Input sheet and move it over to the
Data sheet. I've shown 2 ways to do this:
1) copy the entire row
2) copy individual cells
This code would be run after you've entered new data on the Input sheet.
Then after what ever calculations are run on the Data sheet are completed,
you'd just kind of reverse the data flow (copying from Data sheet back to
Input sheet) to get your results back.


But I'm a bit confused as to why you are using 2 sheets to do this. The
calculations could all be placed on the single "input" sheet, or am I missing
something here? I suppose this is another case where it would have been
helpful if you could have uploaded the workbook so it could be seen. Not
your fault.

If you are unfamiliar with how to add code to a workbook, this web page may
help:
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
just cut the code from here and paste it into a new Module in your workbook
and call the routine with Tools | Macro | Macros then choose the macro and
[Run] it.

Sub MoveToDataSheet()
'by JLatham
'email: 2kmaro @ dslr.net (remove spaces)
'
'presumes new data in it goes to the bottom
'of any input data list
'
Dim InputRow As Long
Dim DataRow As Long
'making sheet names variable
'so that you can change them
'once for your setup and the code
'will still work
Dim InputSheet As String
Dim DataSheet As String
'This next is to identify the
'column on the move-to sheet (Data)
'that is most likely to be longest
'or that will always have data in it
Dim DataColumn As String

'now define the variables for your
'workbook
InputSheet = "InputSheet"
DataSheet = "DataSheet"
DataColumn = "A"

'turn off ScreenUpdating for 2 reasons:
'reduces processing time for large data moves
'keeps screen from jumping from sheet to sheet
Application.ScreenUpdating = False ' hide screen activity
'find last row used on the input sheet
Sheets(InputSheet).Activate
InputRow = Range(DataColumn & "65535").End(xlUp).Row
If InputRow = 0 Then
MsgBox "No data found to move."
Exit Sub
End If
'find first empty row on the data sheet
Sheets(DataSheet).Activate
'test to see if there's room for more
If Not IsEmpty(Range(DataColumn & "65535")) Then
MsgBox "No room left on the Data Sheet"
Exit Sub
End If
DataRow = Range(DataColumn & "65535").End(xlUp).Row + 1
'
'if the columns already line up on both sheets
'you can use code like this
'to copy the entire row in one move
Sheets(InputSheet).Activate
Rows(InputRow & ":" & InputRow).Select
Selection.Copy
Sheets(DataSheet).Activate
Rows(DataRow & ":" & DataRow).Select
ActiveSheet.Paste
Range(DataColumn & DataRow).Select
Sheets(InputSheet).Activate
Application.CutCopyMode = False
Range(DataColumn & InputRow).Select
'
'but if columns do not line up then you
'can do the data transfer cell by cell
Sheets(InputSheet).Activate
Sheets(DataSheet).Range("G" & DataRow) = _
Sheets(InputSheet).Range("A" & InputRow)
Sheets(DataSheet).Range("H" & DataRow) = _
Sheets(InputSheet).Range("B" & InputRow)
Sheets(DataSheet).Range("I" & DataRow) = _
Sheets(InputSheet).Range("C" & InputRow)
Sheets(DataSheet).Range("J" & DataRow) = _
Sheets(InputSheet).Range("D" & InputRow)
'repeat above adjusting the column ID for
'columns on each sheet for each data item to move
'because you get no visual indication that
'anything has happened, you may want to
'provide a message to the user so that
'they know it took place
MsgBox "Data Move Completed"
'
'at this point the Data sheet calculations
'would be performed and then
'a reverse of the data flow provided above
'could be done to move processed data back
'to the Input sheet

End Sub



"daedalus1" wrote:

I have a spread sheet with two worksheets. One is Input Data the other is
Data. The first sheet inputs mileage, LPG gas in litres, cost of gas in
pence, comparison cost of petrol in pence, Any miles done on petrol. Plus A
cell as a button to input the data to the data sheet. I want to move the
data from these fixed cells and put it into the first free cell at the bottom
of the relevant column on the data sheet when I click on the input data
button. The data sheet will then do all of the calculation and will send the
averaged fuel consumption for the input data and the averaged fuel
consumption for ALL input data back to the relevant cells on the input data
sheet. I do not need to see the data sheet at all unless I click on the tab
at the bottom of the screen.

Thanks in advance

--
daedalus1