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

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

0

I have successfully created a form that functions exactly like the one in the tutorial.

https://www.teachexcel.com/excel-tutorial/input-form-to-get-data-and-store-it-in-another-tab-in-excel_1570.html

It works great!

The issue, is that I have a similar table / form to input into but the data really has to be saved column to column instead of row to row for organizational reasons, and I'm having difficulty trying to reverse engineer this VBA to get it to function. I either get erros when validating, or it just copies the form output into the first row of my sheet and then overwrites that row.

Sub Data_Input()
' Insert The data from Entry Tab to Output Tab'

ws_output = "Form Output Tab"

next_column = Sheets(ws_output).Range("A" & Columns.Count).End(xlToRight).Offset(1).Column

Sheets(ws_output).Cells(next_column, 2).Value = Range("RTSNumber").Value
Sheets(ws_output).Cells(next_column, 3).Value = Range("Requestor").Value
Sheets(ws_output).Cells(next_column, 4).Value = Range("GlassCodes").Value
Sheets(ws_output).Cells(next_column, 5).Value = Range("Date").Value
Sheets(ws_output).Cells(next_column, 6).Value = Range("ChargeNo").Value
Sheets(ws_output).Cells(next_column, 8).Value = Range("SoftPoint").Value
Sheets(ws_output).Cells(next_column, 9).Value = Range("AnnealPoint").Value
Sheets(ws_output).Cells(next_column, 10).Value = Range("StrainPoint").Value
Sheets(ws_output).Cells(next_column, 11).Value = Range("DensityPoint").Value
Sheets(ws_output).Cells(next_column, 12).Value = Range("CTEPoint").Value
Sheets(ws_output).Cells(next_column, 13).Value = Range("Blank1").Value
Sheets(ws_output).Cells(next_column, 14).Value = Range("Blank2").Value
Sheets(ws_output).Cells(next_column, 15).Value = Range("Blank3").Value

End Sub

Any help would be appreciated.

Thanks!

excel
MS Office Version: 365 Pro
posted by: SgtBaker (rep: 2) on Mon Mar 22, 2021 at 1:07 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Sgt Baker and welcome to the Forum

To get data into the next free column, change your code as shown in bold below.The first bit gets the next free column in row 2 (Revision 1, since you're not writing data to row 1 with this macro) and in Cells the arguments are rows then columns (you had them reversed I reckon):

Sub Data_Input()
' Insert The data from Entry Tab to Output Tab'

ws_output = "Sheet2"

next_column = Sheets(ws_output).Cells(2, Columns.Count).End(xlToLeft).Column + 1

Sheets(ws_output).Cells(2, next_column).Value = Range("RTSNumber").Value
Also, you can save repeated text using a With/End With statement, as follows (with just an example shown, changes in bold):
Sub Data_Input()
' Insert The data from Entry Tab to Output Tab'

ws_output = "Sheet2"

next_column = Sheets(ws_output).Cells(2, Columns.Count).End(xlToLeft).Column + 1
With Sheets(ws_output)
    .Cells(2, next_column).Value = Range("RTSNumber").Value
    .Cells(3, next_column).Value = Range("Requestor").Value
    .Cells(3, next_column).Value = Range("GlassCodes").Value
    ' (et cetera)
End With
(Everything inside the With loop beginning with just a dot "inherits" the bit in the With statement, in this case the sheet reference).

Hope this helps.

posted by: John_Ru (rep: 6142) on Mon Mar 22, 2021 at 2:30 pm
Discuss

Discussion

p.s. Did you try to find last column with ...xlToRight...because you use the display option of columns numbered right to left (rather thatn the Western style, left to right)? 
John_Ru (rep: 6142) Mar 22, '21 at 2:36 pm
Thank You for the welcome John!
I have applied your changes, and now I do not get any errors, and the cells are outputting to the correct row/column. Thank You
The output is now overwriting the previous entry instead of going to the next Blank column. When I click the "Submit" button with the macro it writes the feilds to Column B over and over. If I change the offset to +2 it then overwrites Column C over and over.
SgtBaker (rep: 2) Mar 22, '21 at 2:48 pm
Ah, you need to write something in the row where the next column is calculated (and I see you don't) or it just sees the same row. I suggest you add 
. Cells(2, next_column).Value = NOW() 
(or DATE() if you don't need the time) but I'll revise my Answer to explain.
John_Ru (rep: 6142) Mar 22, '21 at 3:37 pm
Done, changed it so the next column is calculated for row 2 not 1, so:
next_column = Sheets(ws_output).Cells(2, Columns.Count).End(xlToLeft).Column + 1
John_Ru (rep: 6142) Mar 22, '21 at 3:43 pm
Ha!  Great, that did it! Thank you very much.
SgtBaker (rep: 2) Mar 22, '21 at 3:52 pm
Glad it worked.
Incidentally it always helps contributors if you upload a representative Excel file with your Question - using the Add Files..  button). As you see, answering in the abstract can lead to accidental oversights (or at least by me!)
John_Ru (rep: 6142) Mar 22, '21 at 4:23 pm
Add to Discussion


Answer the Question

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