Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Suggested Courses

604 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