Input Form to Get Data and Store it in Another Tab in Excel

0

Hello everyone

I have followed the tutorial on how to make an input form to get data and store it in another sheet.

I have used the exact code that you have presented (pasted below), just changed some of the names. My only problem is that when I run the macro it moves the data to the last row in the table. How can i make it go on 1st available row instead?

Kind regards,

Gabriel

Sub data_input()
ws_output = "Current"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("Person").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("company_name").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("PO_number").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("GL_Code").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("Commence_date").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Details").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Cost").Value

Range("Person").ClearContents
Range("company_name").ClearContents
Range("PO_number").ClearContents
Range("GL_Code").ClearContents
Range("Commence_date").ClearContents
Range("Details").ClearContents
Range("Cost").ClearContents
If Err = 0 Then MsgBox "Save succesfful", vbInformation
End Sub
Answer
Discuss

Answers

0

Try to change this one line of code. next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row into one that looks like that.

next_row = Sheets(ws_output).Range("A2").End(xlDown).Offset(1).Row

This function will look at cell A2 and continue down until an empty cell is found in that column.

Discuss
0

I was following along with the video and am getting a Run-Time errow "1004":

Application-Defined or Object-Defined error.

I'm trying to get the form to transfer the data. The data fields I want transfered are Date, AFS, Travel Dates, Leave Dates, Comments. (Columns A-E on the Data Sheet) Here is the code.

Sub Store_Data()

'Takes data from one worksheet and stores it in the next empty row on 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" & 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("G6").Value

dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("G8").Value

dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("G12").Value

Discuss


Answer the Question

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