Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Repeat a macro in excel, pasted values update a chart. Keep chart in view while calculations take place.

I have recorded a macro to calculate a sheet (f9), copy a cell, paste that cell value, then copy a range of cells, paste the cell values.  The copied cell that changes value with each calculation [based upon NORM.INV(RAND)] is pasted in a single column one row lower each time.  The copied range of cells also changes with each calculation and that range is pasted in a columns that move sequentially to the right.  The values in the columns are used to create a line chart in the spread sheet.  I've recorded a macro that does what I want, but I only recorded it for 6 iterations.  I'd like 100 iterations.  Also, I need the view to remain on the chart during the calculations, since watching the line plots real time adds to the impact of the chart.  Currently, the focus of the spreadsheet moves to the area of the pasted values, then returns to the chart area based upon my final cell selection.  The attachment shows only the first three calculate, copy, paste operations.
Recorded-Macro.docx
0
ronadair
Asked:
ronadair
  • 3
  • 3
1 Solution
 
SimonCommented:
Please try this... Because it isn't using the 'select' method it should leave the focus alone.

Sub iterate100()
Dim startCell As Range, vertColSource As Range, vertColDest As Range
Dim destCol As Integer, colOffset As Integer
Dim destCell As Range
destrow = 13
colOffset = 4
Set startCell = Range("S6")
Set vertColSource = Range("U13:U48")
For ctr = 1 To 100
    Calculate
    startCell.Copy
    Set destCell = Range("X" & destrow)
    'destCell.PasteSpecial Paste:=xlPasteValues
    destCell.Value = startCell.Value
    Set vertColDest = vertColSource.Offset(0, colOffset)
    vertColDest.Value = vertColSource.Value
    destrow = destrow + 1
    colOffset = colOffset + 1
    DoEvents
Next ctr

Open in new window

0
 
ronadairAuthor Commented:
SimonAdept - I copied and pasted your macro got the error message

Compile error:

Expected End Sub
0
 
SimonCommented:
Hi Sorry, I missed that line when I copied it... try again.
Sub iterate100()
Dim startCell As Range, vertColSource As Range, vertColDest As Range
Dim destCol As Integer, colOffset As Integer
Dim destCell As Range
destrow = 13
colOffset = 4
Set startCell = Range("S6")
Set vertColSource = Range("U13:U48")
For ctr = 1 To 100
    Calculate
    Set destCell = Range("X" & destrow)
    destCell.Value = startCell.Value
    Set vertColDest = vertColSource.Offset(0, colOffset)
    vertColDest.Value = vertColSource.Value
    destrow = destrow + 1
    colOffset = colOffset + 1
    DoEvents
Next ctr
End Sub

Open in new window

Iterate-100.xlsm
I've posted the working example workbook too.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ronadairAuthor Commented:
Excellent!  Works perfectly.  One more question, if I may.  What do I delete from the macro if I need to eliminate the select, copy, paste of the changing values in the cell S6 that get pasted into the X column?  In other words, I only need the changing values copied and pasted from the range of cells U13:U48.
0
 
SimonCommented:
You would comment out these two lines (rows 11 & 12 in my code listing above)

    Set destCell = Range("X" & destrow)   'this is an object reference assignment that isn't needed if you're not using it
    destCell.Value = startCell.Value           'this is the line that 'copies' the value from S6 to a single cell in column X

I'm kind of intrigued about what you're using this for. Can you tell us or is it top secret? :)
0
 
ronadairAuthor Commented:
Thank you!  Fantastic & very timely!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now