Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
excel
MS Office Version: 2016
posted by: GPetcu on Tue Mar 10, 2020 at 8:57 am
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.

posted by: Variatus (rep: 4889) on Tue Mar 10, 2020 at 9:05 pm
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

posted by: geneaguirre1 on Thu Mar 12, 2020 at 6:54 pm
Discuss


Answer the Question

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