Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2015-01-08
6
Medium Priority
?
293 Views
Last Modified: 2015-01-08
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
Comment
Question by:ronadair
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40539227
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
 

Author Comment

by:ronadair
ID: 40539255
SimonAdept - I copied and pasted your macro got the error message

Compile error:

Expected End Sub
0
 
LVL 18

Expert Comment

by:Simon
ID: 40539267
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ronadair
ID: 40539318
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
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40539330
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
 

Author Closing Comment

by:ronadair
ID: 40539333
Thank you!  Fantastic & very timely!!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question