# Enter Value in One Worksheet and Generate Many Values in Another Sheet

This Excel training video is based on the following query:
Hi, First of all, thank you for all the interesting excel VBA tutorials you’ve made! I’m a Swedish student from Malmö, studying bachelor in civil engineering and is now writing my candidate paper. In my paper I thought to include an Excel file but I’ve got some problems with macros and VBA. What I want to do: The user gives a number (days) and excel (the macro) generates a list with the same amount of numbers as it is days (a list with 1,2,3…) in another sheet. the user will also write a price per unit that is supposed to complete the list next to the numbers. I don’t know if my explanation above is any good, so I’ll give you my code. It doesn’t work and I would be very glad if you could help me.
CODE:
Dim number, x, x1, antaltimmar As Integer
x1 = 1 ‘row 1 (so excel knows)
‘I’ve made a list starting in L1 with 1 L2 with 2 and so on. x = 4 ‘row 4, this is the starting row, where I want excel to start paste info. number = 1 ‘it is always at least 1 day.
Do Until number = antaldagar ‘Amount of days should be the same as in the list.
Worksheets(“Blad1”).Select ‘chose sheet1 to get info from
antaldagar = Range(“C10”) ‘antaldagar value from C10.
Worksheets(“blad1”).Range(Cells(x1, 12)).Copy ‘copy from cell Lx1
Worksheets(“blad2”).Range(“Cells(x, 3)”).Paste ‘paste from cell Lx1 to column C (3) row x.
x = x + 1 ‘next row
x1 = x1 + 1 ‘next row
number = number + 1 ‘add 1 number until it’s equal to number of days
Loop
End Sub
Amanda W ‘

Our code looks like this and uses a ‘for loop’ attached to a command button to automate the work of entering the number of days as a single value in one worksheet and placing the complete list of values generated from the entered value into another worksheet.
Private Sub CommandButton1_Click()
Dim days As Integer
days = Sheet1.Range(“B1”).Value ‘ we capture the value entered in cell B1 into the variable ‘days’
Worksheets(“Sheet2”).Select ‘ next we select the worksheet2
x = 4 ‘we define the row where we’ll start entering data automatically
y = 3 ‘ we define the column where the data will go automatically
For i = 1 To days ‘we start the loop at 1 and the values go up to the number captured in the variable
Sheet2.Cells(x, y) = i ‘ we assign a value to cells(4,3) i. e. cell C4 automatically
x = x + 1 ‘ the macro goes to the next row. Example: Cells(5,3) i. e. cell C5 and so on …
Next i ‘looping should continue till i=days
Worksheets(“sheet2”).Range(“D4”).Select ‘ we now remain in sheet2 but select cell D4 to continue our work
End Sub

Watch the video:

This site uses Akismet to reduce spam. Learn how your comment data is processed.