Selected Answer
Hi Wendy,
Yours is really a magnificent effort. But it is a huge effort tilted at a minor matter. In effect, your code simulates your fingers on the keyboard. The macro recorder encourages this for lack of a better way of teaching but the fact is a lot simpler. Just imagine a worksheet. Call it "MySheet". Encoding this idea would look like this:
Dim Ws as Worksheet
Set Ws = ThisWorkbook.Worksheets("MySheet")
ThisWorkbook defines the workbook which contains the code. ActiveWorkbook could be the same or another one (the one you last clicked in). That's why using the ActiveWorkbook is dangerous. But not saying anything at all is even more so because VBA will apply the code to the ActiveWorkbook even while you aren't aware of it. If you don't want to use ThisWorkbook, it is best practise to define the intended workbook by name.
Going back to the above code, your object variable Ws now defines a million rows, thouands of columns, many millions of cells. Guess at the meaning of this code.
Ws.Cells(10, 1).Value = ""A10"
I know you guessed correctly. It's really so easy. You can call any cell on the worksheet with two numbers: the first is the row, the second the column. You could use different annotations as shown below.
Ws.Cells(10, "A").Value = "A10"
Ws.Range("A10").Value = "A10"
While useful at times these alternatives are inferior to using numbers because you can't do calculations with implied numbers. You need real numbers for that, for example, if you want to calculate the location of a cell based on supplied parameters.
In the above example you have assigned a value to a cell. Observe that you never selected it. You didn't select the worksheet. In fact, you didn't even need to select or activate the workbook. All of that was done with one line of code, Set Ws = ThisWorkbook.Worksheets("MySheet"). Note that the simple Range("A10") or Cells(10, 1) specifies a cell on the ActiveSheet. It's much better to specify the sheet you mean rather than letting Excel guess your intention, like, Ws.Range("A10").
Of course, you can assign the value of a cell on one sheet to the Value property of a cell on another. That is straight-forward, too.
Dim WsForm as Worksheet
Set WsForm = ThisWorkbook.Worksheets("MyForm")
Ws.Cells(10, 1).Value = WsForm.Cells(4, 6).Value ' = F4
Note that Value is a Range object's default property. Therefore you can omit it (as you frequently do in your code). Guess at the difference between the following two lines of code.
Ws.Cells(10, 1).Value = WsForm.Cells(4, 6)
Ws.Cells(10, 1) = WsForm.Cells(4, 6)
The first line sets the Value property of the range Ws.Cells(10, 1). VBA will therefore take the Value property from the other range and assign it. The second line makes the range Ws.Cells(10, 1) equal to WsForm.Cells(4, 6). All properties are transferred, including font colour, fill, conditional formatting - everything, including the Value.
The gist of this is that a Range (a cell is a Range, of course, with just one cell) has many properties besides its value. One of these is the Formula property. Now, if you can guess what the line of code below does you have successfully absorbed today's lesson.
Ws.Cells(10, 1).Formula = WsForm.Cells(4, 6).Formula
Happy programming!