0

I recorded a macro to better understand my process. This macro is shown below in the first part. The second part is my attempt to make this thing faster.

Current Sequence of Events

  1. Insert asset name in cell D3, in a tab titled "Live" (cell D3 is a dropdown list with 50+ assets - more on this in next section titled "Steps We Can Improve")
  2. Calculate new asset by using Application.CalculateFull
  3. Wait until the "Live" tab is done calculating to move to next line of code
  4. Wait 15 seconds (more on this in next section)
  5. Copy the newly iterated data in the "Live" tab
  6. Create a new worksheet titled with the asset's name
  7. Paste the data from the "Live" tab as values into the newly created worksheet
  8. Repeat steps 1-7 until all assets have been calculated

Steps to Improve

  1. instead of inserting an asset name in cell D3, which requires a lot of code, I would like to loop through the list found within cell D3 (a data validation list which sources asset names from a master list on another worksheet).
  1. I added time to the code because I thought the model was not fully iterating for certain assets. This was wrong - it never iterated correctly with the addition of time. So, it is likely a formula error? The formula is listed below, and it is showing a #VALUE error for roughly 20 assets - but only when I run the macro. Outside of the macro, this formula populates correctly.
Sub SlowMACROv3()

  ' Turns off screen updating and auto calcs which helps to speed macro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

  ' Turns off screen updating and auto calcs which helps to speed macro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual    

    Sheets("Live").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "309"
    Application.Calculate
If Not Application.CalculationState = xlDone Then DoEvents
    Application.Wait (Now + TimeValue("0:00:15"))
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Sheets("Live").Select
    Sheets("Live").Copy Before:=Sheets(1)
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "310"
    Application.Calculate
If Not Application.CalculationState = xlDone Then DoEvents
    Application.Wait (Now + TimeValue("0:00:15"))
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

  ' Turns on screen updating and auto calcs
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

My second attempt works, but the code is just as long. Also a formula throws an error for some assets and can be found at the very bottom. 

Sub SlowishMACROv1()

  ' Turns off screen updating and auto calcs which helps to speed macro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

  ' Cycle all assets and create new worksheets as values macro
    Sheets("Live").Copy Before:=Sheets(1)
    Range("D3").FormulaR1C1 = "309"
    Application.Calculate
    If Not Application.CalculationState = xlDone Then DoEvents
    Range("A1:XFD1000").Value = Range("A1:XFD1000").Value

    Sheets("Live").Copy Before:=Sheets(1)
    Range("D3").FormulaR1C1 = "310"
    Application.Calculate
    If Not Application.CalculationState = xlDone Then DoEvents
    Range("A1:XFD1000").Value = Range("A1:XFD1000").Value

  ' Turns on screen updating and auto calcs
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic     

End Sub

Formula: =+IF(INDEX('Reversion Dashboard'!$I:$I,MATCH($D$3,'Reversion Dashboard'!$C:$C,0))=1,I331*(1+(RATE($E$325,,$I331,-('Reversion Schedule'!$K$19)))),"")

This formula works in the "Live" tab except for assets that trigger the =1 portion within the IF True section, whenever the macro is run. Why does this formula work every time we switch to a different asset, but not when I run the macro?

Izaak van Dongen
  • 2,450
  • 13
  • 23
MEDITATED
  • 3
  • 3
  • Mods gave me a flag in another post - check out this link for a potentially relevant idea of how we are trying to accomplish the above - https://stackoverflow.com/questions/46548571/nested-for-each-to-loop-through-dropdown-and-copy-paste/59396476?noredirect=1#comment104982857_59396476 – MEDITATED Dec 18 '19 at 17:13
  • 1
    What exactly doesn't work? Add sheet references to your code to avoid ambiguity. – SJR Dec 18 '19 at 17:23
  • 1
    And read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Dec 18 '19 at 17:27
  • @SJR - It is very tedious to add and or delete assets to my current code. Felt like there might be a better way to reference and cycle the range of assets found in the dropdown list (cell D3 on the "Live" tab). In addition, I am receiving a #VALUE error in a formula when cycling through a certain type of asset... Would it help if I posted the formula? – MEDITATED Dec 18 '19 at 17:30
  • Yes, post the formula and values when it errors. So you want to insert a set of values into D3, take the answers from resultant calculations and copy to another sheet? – SJR Dec 18 '19 at 17:31
  • @braX - thank you for the formatting help. SJR - just saw your link - thank you for the advice! I will go ahead and add the formula trouble to my post. – MEDITATED Dec 18 '19 at 17:32
  • @SJR - To give a more clear pic of what's happening, in the model - there's a tab with raw data that populates the "Live" tab whenever a specific asset is chosen from the dropdown list in D3... D3 is just a set of names that can be chosen to iterate the "Live" tab... Fortunately, the names are just numbers which fall anywhere between 300 and 450. E.g. I choose asset "420" from the list in cell D3, then the "Live" tab's formulas iterate based on the chosen asset by using the data dump tab and a series of other tabs to arrive at end numbers. I then copy/paste these values into a new worksheet – MEDITATED Dec 18 '19 at 17:38
  • I think that's basically what I was saying. Your code appears to be inserting the values into D3 though rather than them being chosen from a dropdown. Why are you covering the range to column XFD? – SJR Dec 18 '19 at 17:40
  • @SJR - I would take this to chat but I'm too new! Glad we're on the same page. I didn't know I could choose the assets from a dropdown. I was inserting the names into D3 because it still resulted in a given asset populating the "Live" tab correctly. Can you help me switch the code to pull from the dropdown list? My fear is that the code will populate worksheets with blank data if an asset is not listed in the range... And the range to column XFD is pointless...I just wanted to copy/paste values of the entire sheet to a new worksheet but thought it would possibly save time...macro runtime=40min – MEDITATED Dec 18 '19 at 17:50
  • How many formulae do you have on your sheet? You can have a dropdown using data validation or a combobox. – SJR Dec 18 '19 at 17:51
  • @SJR - in cell D3, we have a Data Validation which states "='Asset Dashboard'!$C$6:$C$570" so I guess the Asset Dashboard has a list of 564 potential assets. Fortunately, there are only 50-100 assets that populate the 564 rows. – MEDITATED Dec 18 '19 at 18:15
  • But do you want this code run just when D3 is changed or to run through the whole list of 564 items? – SJR Dec 18 '19 at 18:24
  • 1
    @MEDITATEDwelcome to SO, most of the time newcomers feel being treated tough, it happened to me too, but if you tough it out this forum can be very useful. You just need to learn the rules here. I had a quick look at your question, it is hard to understand what you really want to do, it is always better to listen to the comments of the more senior members and edit your post to increase your chance of getting an answer. If you are using VBA, there is no need to use formulas and slow down the workbook, does your sheet include formulas? in a simple word explain what is your final goal is? – Ibo Dec 18 '19 at 18:47
  • @SJR - I only want the code to iterate the assets that fall within the list of the 564 items, meaning there will be ~400 blanks and I want to be sure to exclude them. It would be ideal to have the code only iterate/create new worksheets whenever a value is found within the list. E.g. the list starts at asset "309" but jumps numbers, all the way up to an asset titled "450". There are roughly 75 assets not listed between 309 and 450. – MEDITATED Dec 18 '19 at 19:05
  • @Ibo - thanks for the big welcome! I appreciate tough words and want to quickly learn! I think my questions above are very confusing - to help clarify your comment, I do not want to add a formula to the code. I want the code to loop through all of the listed assets in cell D3, so that I do not have to list each individual asset in my code starting at asset "309". Cell D3 uses a list via Data Validation, so I might as well use that list to automatically iterate instead of using the current insert method that I coded. Also, the formula that I put in the post is breaking when an asset triggers 1 – MEDITATED Dec 18 '19 at 19:09
  • the formula issue is separate from the long code issue - editing post now to make it more clear – MEDITATED Dec 18 '19 at 19:10
  • 1
    Do you have a master list of the asset and you want to filter the assets based on the selection in a cell? one way to help us is to show us the input and the output, it takes time to build a good question, but it pays off quickly – Ibo Dec 18 '19 at 19:19
  • @ lbo- yes, there is a master list of the assets, and I want to loop through all of the assets in order to eventually create new worksheets of all the assets. you have all been very helpful and i am incredibly grateful. i am running SJR's code at the moment and will report back once complete. let me know if the updated post is helpful! – MEDITATED Dec 18 '19 at 19:53

1 Answers1

0

Am tempted to suggest you close this question and start again as the comments are long. Read [ask] and [mcve] and perhaps post a screenshot as remember that none of us know anything about your set up.

However, see if this code broadly does what you want - it's not entirely clear as Ibo says.

Sub x()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim r As Range, ws As Worksheet

For Each r In Worksheets("Asset Dashboard").Range("C6:C570") 'go through each cell in DV list
    If Len(r) > 0 Then                                       'only do something if cell not empty
       Worksheets("Live").Range("D3").Value = r.Value           'transfer value to D3 of Live
       Application.Calculate
       Set ws = Worksheets.Add                                  'add new sheet
       Worksheets("Live").UsedRange.Copy
       ws.Range("A1").PasteSpecial xlValues                     'copy values only from Live to new sheet
    End if
Next r

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Thank you for providing your code! I just attempted to update my post with better talking points. Will try your code now! – MEDITATED Dec 18 '19 at 19:46
  • I forgot an end if - try it now. – SJR Dec 18 '19 at 20:33
  • New bug, shows a "Worksheet" used error for this line: Worksheets("Live").UsedRange.Copy... when running, the "Live" sheet is becoming titled asset "309" and the code is then unable to reference the correct sheet, if not mistaken – MEDITATED Dec 18 '19 at 21:01
  • Somebody had added a macro to change the "Live" sheet name - just deleted it and am now running the code! – MEDITATED Dec 18 '19 at 21:27
  • it worked!!!!!!! that is very impressive. Is there a way to keep the formatting from the "Live" tab? It populates all of the new sheets correctly, but my boss is a stickler for formatting... more importantly, is there a way to make the title of the newly calc'd worksheet the same as the asset? – MEDITATED Dec 18 '19 at 21:41
  • Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D3")) Is Nothing Then ActiveSheet.Name = ActiveSheet.Range("D3").Value End If End Sub – MEDITATED Dec 18 '19 at 22:09
  • Hey everyone, i managed to fix the formatting and other issues - cannot thank all of you enough! – MEDITATED Dec 19 '19 at 00:09
  • I'm a bit confused because nothing in my code changes the name of a worksheet. You could just add `ws.name=r.value` into the loop. There is also a pastespecial option to include formatting I think. If it's helped, please consider accepting the answer. – SJR Dec 19 '19 at 11:01
  • 1
    I made a couple of additional changes to code, including formatting and naming conventions of ws. This has been a great learning experience - will gladly accept your answer! – MEDITATED Dec 20 '19 at 15:43
  • Hey everyone this code has been a life save, thank you all again for your input. Quick follow up question - is it possible to paste values for font colors in green and keep other font colors (e.g. black and white) as formulas? – MEDITATED Jan 29 '20 at 15:27
  • I found color.index and some other helpful code. Yet, creating multiple IFS within the code has proven difficult. Example 1: if font color is green, then paste as values, if font color is green, change green font color to blue, if font color is white or black, then paste as formulas. – MEDITATED Jan 29 '20 at 15:35
  • Posed font color question here: https://stackoverflow.com/questions/59971645/how-to-code-vba-to-paste-values-or-formulas-based-on-font-color – MEDITATED Jan 29 '20 at 16:36