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

VBA code to copy formula's

0

Hi there,  I have created a form in Excel 2016 (called "FORM") and have VBA coding to copy the information into a "MASTER" worksheet in the same workbook.  The VBA code then clears the contents of the "FORM".  See code below.

In the "MASTER" I have three formulas contained cells I4, K4, J4 that I want to copy.  I've tried a few things but they don't work...please help...thank you.

Private Sub CommandButton1_Click()
Dim Name As String, QADate As String, Securitycompleted As String, Professional As String, Queryresolved As String, MySCifapplicable As String, Feedbackgiven As String, TeamLeaderadvised As String
Worksheets("Form").Select
Name = Range("F4")
QADate = Range("F6")
Securitycompleted = Range("I8")
Professional = Range("I10")
Queryresolved = Range("I12")
MySCifapplicable = Range("I14")
Feedbackgiven = Range("I18")
TeamLeaderadvised = Range("I20")
Worksheets("MASTER").Select
Worksheets("MASTER").Range("A6").EntireRow.Insert
Worksheets("MASTER").Range("A6").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = QADate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Name
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Securitycompleted
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Professional
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Queryresolved
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = MySCifapplicable
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Feedbackgiven
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TeamLeaderadvised
Worksheets("Form").Select
Worksheets("Form").Range("F4,F6,F8,F10,F12,F14,F18,F20").ClearContents


End Sub
Answer
Discuss

Discussion

Press the Edit button under your post. Select the code. Then press the Code button in the header of the edit panel. That will add HTML Code tags around your code. When you upload your modified post the code will appear in a special frame which support copying it at one click. I could then easily copy it and paste it to my computer for testing, correction or annotating.
Variatus (rep: 4889) Dec 12, '18 at 7:35 pm
Done..thank you :)
wendyh Dec 12, '18 at 8:49 pm
Great! Thank you. Have you been able to follow my answer? Please comment on the answer if you need more help.
Variatus (rep: 4889) Dec 13, '18 at 4:07 am
Add to Discussion

Answers

0

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!

Discuss


Answer the Question

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