Having trouble saving data from Excel form!


I followed the "Idiot-Proof Forms in Excel" videos to create my own excel form, but when I got to Module 2 of the Macro where you put in the code to save the data from the form to another worksheet, one of the lines of code is giving me the 1004 error.

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(x1Up).Offset(1).Row

This is the part of the code that adds the data inputted to the next available row on the spreadsheet. I checked and rechecked my code with the code in the video and even tried to change it a little bit but nothing seems to be working. I was wondering if I'm doing something wrong or if there's another way to write the code that'll have the same function.

Here is my whole Module 2 for reference:

Sub Store_Data()
'Takes data from one worksheet and stores it in the next empty row of another worksheet

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer

Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Data")

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(x1Up).Offset(1).Row

dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F10").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F12").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F14").Value

End Sub


Hi Kim, can you tell me which video you got the file from that had this error in it so I can make sure the error is not saved in the file? I checked a few of them already and couldn't find this error.
don (rep: 1969) Aug 15, '19 at 9:59 am
You didn't make the error, it was all me. Instead of downloading the file I just watched your video and made my form at the same time next to it and I thought that the "l" was a "1"
KKimm (rep: 2) Aug 26, '19 at 3:47 pm
Add to Discussion


Selected Answer

XLUP, not X one UP...

If you Compile from the VBE View menu before a Run, it will catch many syntax errors like that.



Thank you so much! I can't believe I missed that! It works perfectly now!
KKimm (rep: 2) Aug 14, '19 at 4:30 pm
Great catch!
don (rep: 1969) Aug 15, '19 at 9:57 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login