VBA: How to put the calculated solution into a new worksheet?

Question

I have a very basic problem, but somehow just don't find the solution to it. I tried to write a little program, which takes the given stock prices from a worksheet and calculates the moving average of it over a given period of time.

So far that was not a problem.

My problem now is, that I want the solution i.e. the moving average in a new worksheet in column B and the equivalent stockprices in column A.

I already wrote the code to create a new worksheet as follows:

Sub CopyToNewSheet()
Selection.Copy
Sheets.Add(After:=Sheets(1)).Name = "MovingAverage" 
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

This works fine so far. The next part of the program should now be the calculation of the moving average. I tried this, which worked as well:

Sub CalculateMA(maLength As Integer)

Application.ScreenUpdating = False
Columns("B:B").Delete
Range("B1").Value = "MA" + CStr(maLength)
Range("B1").Offset(maLength, 0).Select

Do While IsEmpty(Selection.Offset(0, -1)) = False
    Selection.Value = WorksheetFunction.Average(Range(Selection.Offset(-maLength + 1, -1), Selection.Offset(0, -1)))
    Selection.Offset(1, 0).Select
Loop
End Sub

The problem here is that this does not put the calculated solution into the new worksheet, but into the old one.

Do you have any ideas, what I have to change, so that the solution is posted into the other worksheet?

Thanks a lot.

Your help is appreciated.

did you tried my suggestion, know about macros? – João Pinho Jan 12 '14 at 00:24 — João Pinho, Jan 12 '14 at 00:24

score 1 · Answer 1 · answered Jan 11 '14 at 23:51

1

You should be able to specify the worksheet you want to modify in front of your range:

 Worksheets("MovingAverage").Range("B1").Value = "MA" + CStr(maLength)

Do the same when you select the range, and it should work how you intended.

answered Jan 11 '14 at 23:51

thunderblaster

918
11
27

score 1 · Answer 2 · answered Jan 11 '14 at 23:58

1

Inside Sub CopyToNewSheet, before the ActiveSheet.Paste line, add this :

WorkSheets("MovingAverage").Activate

The reason behind this, is because even though you just added a sheet, it's not yet the ActiveSheet.

answered Jan 11 '14 at 23:58

Bernard Saucier

2,240
1
19
28

it worked, but instead of the "Sub CopyToNewSheet" I wrote it into the "Sub CalculateMA" part. – user3186278 Jan 12 '14 at 12:21

score 0 · Answer 3 · answered Jan 11 '14 at 23:49

0

Suggestion: after the calculation, look at the result, now turn macro recording on, perform your copy to the destination sheet, now stop the macro. Your code is ready! It's done by excel it self.

Macro recording gives you a huge boost when programming in Excel.

Try it out.

Here is a link how to do a macro if you don't know how. I really encourage you learning it, basically most of your code in power sheets can be generated for you through macros, also it helps you learn excel vba language much faster.

answered Jan 11 '14 at 23:49

João Pinho

3,725
1
19
29

While this is on the surface good advice, you will learn lots of bad habits from studying recorded macros - think of all the `.Select` statements and such. You risk learning a lot of inefficient techniques, and will miss out on a lot of good techniques. I recommend it as a last resort only; not "to learn the language faster". It is a shortcut, but not to the right destination. – Floris Jan 12 '14 at 08:32
@Floris Agree, but it helps to understand at the beginning :) – João Pinho Jan 12 '14 at 12:56

score 0 · Accepted Answer · answered Jan 12 '14 at 07:55

You run into a very common problem: when you don't specify what worksheet you are talking about, you can't guarantee which one Excel will pick. The right approach is to tell Excel explicitly. As a benefit, when you use objects rather than .Selection, things are faster... So when you create the new worksheet, give it a name; then you can refer to it.

Try the following modification of your last piece of code:

Sub CalculateMA(maLength As Integer)

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim sourceCell As Range
Dim destCell As Range

Application.ScreenUpdating = False

Set ws1 = ActiveWorkbook.Sheets("stockData")    ' whatever the name is...
Set ws2 = ActiveWorkbook.Sheets("movingAverage")

ws2.Columns("B:B").Delete
ws2.Range("B1").Value = "MA" + CStr(maLength)

Set sourceCell = ws1.Cells(maLength, 1)
Set destCell = ws2.Cells(maLength, 2)

Do While IsEmpty(sourceCell) = False
    destCell.Value = WorksheetFunction.Average(Range(sourceCell.Offset(-maLength + 1, 0), sourceCell))
    Set sourceCell = sourceCell.Offset(1, 0)
    Set destCell = destCell.Offset(1, 0)
Loop

Application.ScreenUpdating = True

End Sub

I think this does what you were after.

VBA: How to put the calculated solution into a new worksheet?

4 Answers4