Trouble with macro to enter data in a table

[6741bob]

I am trying to take data (both input and calculated) from one sheet and enter it to the next row on a table in another sheet and I keep getting the Error message "Run time error 1004'

Here is the data on the "input" sheet I am trying to store on "Sheet4"

Date	2/23/2023	mm/dd/yyyy
	Values	Units
Height	186	Cms
	186.00	cms	store this
Weight	200	lbs
	90.9	kgs	store this
Waist	38	inches
	96.52	cms	store this
Hips	36	inches
	91.44	cms	store this
Chest	186	Cms
	186.00	cms	store this
Suprailiac	15.00	mms
	15	mms	store this
% Body Fat	22.8%	%	store this
RHR (7dy)	55	bpm	store this
BP-Systolic	120	mmhg	store this
BP-Diastolic	80	mmhg	store this
Waist/Height Ratio	0.52		Calculated, store this
BMI	26.2		Calculated, store this
Lean Body Mass	154.4	lbs
	70.2	kgs	Calculated, store this

Here are the headings in row 1 on Sheet4 with Date in Column A

Date

Height cms

Weight kgs

Waist cms

Hips cms

Chest cms

Suprailiac mms

% Body Fat

RHR (7dy) bpm

BP-Systolic mmhg

BP-Diastolic mmhg

Waist/Height Ratio

BMI

Lean Body Mass kgs

Here is the macro that I am trying to run and the debugger indicates it is failing on the nextRow command. Any idea why this won't run?

Sub Store_Measured_Data()
'takes data from input form worksheet and stores it in the next empty row on a worksheet

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Long

' Set sheet variables so we can use those rather than hardcoding
Set sourceSheet = Sheets("Input")
Set dataSheet = Sheets("Sheet4")

' get the next empty row from the data sheet
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(x1up).Offset(1).Row

'Input the form values into the data sheet
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("e6").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("e8").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("e10").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("e12").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("e14").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("e16").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("e18").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("e20").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("e23").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("e25").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("e27").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("e29").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("e31").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("e34").Value

'Clear the form
sourceSheet.Range("e6").Value = ""
sourceSheet.Range("e8").Value = ""
sourceSheet.Range("e10").Value = ""
sourceSheet.Range("e12").Value = ""
sourceSheet.Range("e14").Value = ""
sourceSheet.Range("e16").Value = ""
sourceSheet.Range("e18").Value = ""
sourceSheet.Range("e20").Value = ""
sourceSheet.Range("e23").Value = ""
sourceSheet.Range("e25").Value = ""
sourceSheet.Range("e27").Value = ""
sourceSheet.Range("e29").Value = ""
sourceSheet.Range("e31").Value = ""
sourceSheet.Range("e34").Value = ""

End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Any idea why this won't run?

Yes, you have used x1up not xlup

 

[6741bob]

duh, good eye, thx very much

 

[Peter_SSs]

You're welcome.

 

[Peter_SSs]

I should have mentioned: Excel vba would have picked that error up for you if you had Option Explicit at the top of your module.
I would recommend always having that option set and vba will do it automatically for you if you make this setting in the vba window.