copy contents of last row to a specific cell in another worksheet

usually you would do something like this:

Sheets("Sheet1").Range("A1")=Cells(Rows.Count,1).End(XlUp)

it will put in your Sheet1 cell A1 the value of the last row in column A.

The problem is I need the data of the last row of columns in sheet 1 to always go into a fixed cell of sheet 2

I.e. Last row of column A in sheet 1 to go into cell B56 of sheet2, last row of column B in sheet1 to go into cell Z5 of sheet13 etc

I have 107 columns in sheet1 to collate data from my user form and 15 other sheets with specific cells to be populated from sheet1 that make up the report

Here is an example. The userform (formMeLoc) puts the entries into the worksheet called Data always populating the next row available.

Then from worksheet called Data I want to put the last row entry of:

column A (Name) into Sheet 2 cell C6
column B (Age) into Sheet 2 cell C8
column C (Phone No) into Sheet 3 cell G18

I would like this to be automatically done or I am happy to have a button with vba to populate sheet 2 and 3 once the userform has been closed.

Each time the userform is used it will then create the report (which consists of sheet2 and sheet3).

I hope that makes more sense.

how about this modification of your cmdAdd_Click() macro:

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter your name"
Exit Sub

End If

With ws

.Cells(iRow, 1).Value = Me.txtName.Value
.Cells(iRow, 2).Value = Me.txtAge.Value
.Cells(iRow, 3).Value = Me.txtPhone.Value
'Here you place your data in your specific sheets and cells
Sheets("Sheet2").Range("C6") = Me.txtName.Value
Sheets("Sheet2").Range("C8") = Me.txtAge.Value
Sheets("Sheet3").Range("G18") = Me.txtPhone.Value
End With

Me.txtName.Value = ""
Me.txtAge.Value = ""
Me.txtPhone.Value = ""

End Sub