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
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
248 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

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 500 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

Suggested Solutions

Title # Comments Views Activity
maro to copy and paste from one worksheet to another based on a condition 11 54
Copy a row 12 64
VBA Shell can't Find Word document 11 111
transition to visual .net from vb6 5 37
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

839 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