In the menu that drops down select Insert and Module. In the Project Explorer window on the left, right-click on the name of the workbook where you want to add the code. With the properly written code making this change - or any other change - is child's play.įrom the Developer tab in the ribbon click Visual Basic (first button on the left) to open the VB Editor. The two lines of original code don't have this capability. The variable Rs would need to be supplied by another function.

I draw your attention to the variable Rt which is supplied by a function. You will notice the difference when you start to work with it. That my seem like an awful lot of code to do the work of just two lines. ' in a blank column the last used row is 0 (= none) If Ws Is Nothing Then Set Ws = ActiveSheet ' return the result from the currently active sheet. ' Return the number of the last non-blank row in column Col. Private Function LastRow(Optional ByVal Col As Variant, _ Ws2.Cells(Rt + 1, NwsTarget).PasteSpecial Paste:=xlValues After you do this, this would be the result:.

So you take them out of the code where they are easy to find and therefore easy to modify. Even if they are not really variable every time you use the code they may change whenever you modify your workbook. The rule says that you shouldn't hard-code variables. Worksheets('Sheet 2').Cells(7, 3).PasteSpecial Paste:=xlValuesĮnd SubObserve that Cells(7, 3) specifies R and Column = Row 7, column 3 (= column C) = C7

Record macro in excel 2013