Avoid Copy PasteValue statments

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
178
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
Hi
Is there a way tocapture the array in AA26:AH43 and then put the values abck to the sheet at theend of the sub

I have a routinethat triggers a Stats App (BERT) by changing Go to 1 and then back to 0
I want to capturethe array within the trigger state
But I want to avoidthe paste values statement before GO state is 0???

Below is code withthe Copy PasteValue part shown
The code is in a Module not an Object

Any guidance gratefully received…. Paul

Code:
[FONT=Calibri]Sub RunCalcOnce()[/FONT]

[FONT=Calibri]'   RUN CALCULATION ON/OFF from Calc Menu onRibbon[/FONT]
[FONT=Calibri]    Application.ScreenUpdating = False[/FONT]
[FONT=Calibri]    Sheet13.Range("AA2:AH19").Clear[/FONT]

[FONT=Calibri]    Set Go = Sheet22.Range("go")[/FONT]
[FONT=Calibri]    IfApplication.COMAddIns("BertRibbon.connect").Connect = True Then[/FONT]

[FONT=Calibri]        If Not Range("MRVC") Then[/FONT]
[FONT=Calibri]            Application.StatusBar = "StatsApp now Running"[/FONT]
[FONT=Calibri]        End If[/FONT]

[FONT=Calibri]        If Go = 0 ThenSheet22.Range("go") = 1[/FONT]

[FONT=Calibri]    Else[/FONT]
[FONT=Calibri]        Application.StatusBar = "Stats Appdoes not appear to be switched on !!"[/FONT]
[FONT=Calibri]        Exit Sub[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]    If Sheet13.Range("AA27") <>0 And Len(Sheet13.Range("AA27")) > 6 Then[/FONT]
[FONT=Calibri]       Sheet13.Range("AA26:AH43").Copy[/FONT]
[FONT=Calibri]       Sheet2.Range("CE4").PasteSpecial xlPasteValues[/FONT]
[FONT=Calibri]    End If[/FONT]

[FONT=Calibri]    Sheet22.Range("go") = 0[/FONT]
[FONT=Calibri]    Application.ScreenUpdating = True[/FONT]

[FONT=Calibri]End Sub[/FONT]

 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Is this what you mean?
Code:
Sub RunCalcOnce()
   [COLOR=#0000ff]Dim Ary As Variant[/COLOR]
'   RUN CALCULATION ON/OFF from Calc Menu onRibbon
    Application.ScreenUpdating = False
    Sheet13.Range("AA2:AH19").Clear

    Set Go = Sheet22.Range("go")
    If Application.COMAddIns("BertRibbon.connect").Connect = True Then

        If Not Range("MRVC") Then
            Application.StatusBar = "StatsApp now Running"
        End If

        If Go = 0 Then Sheet22.Range("go") = 1

    Else
        Application.StatusBar = "Stats Appdoes not appear to be switched on !!"
        Exit Sub
    End If

    If Sheet13.Range("AA27") <> 0 And Len(Sheet13.Range("AA27")) > 6 Then
     [COLOR=#0000ff]  Ary = Sheet13.Range("AA26:AH43").Value[/COLOR]
       
    End If

    Sheet22.Range("go") = 0
    [COLOR=#0000ff]Sheet13.Range("AA26:AH43").Value = Ary[/COLOR]
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Why would you need an array? it is a continuous range.
If you just want to avoid the pastespecial then try...

Code:
   With Sheet13.Range("AA26:AH43")
        Sheet2.Range("CE4").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
 
Upvote 0
Thanks Fluff..... that worked fine although I think you meant the target range was different
Array called BertData

Code:
    If Sheet13.Range("AA27") <> 0 And Len(Sheet13.Range("AA27")) > 6 Then
        BertData = Sheet13.Range("AA26:AH43").Value
    End If
    Sheet22.Range("go") = 0
    Sheet2.Range("CE4:CL21").Value = BertData
 
Last edited:
Upvote 0
Thanks Mark858, and I see this would have worked but I need to collect the dat before G=0 and I wanted to update the sheet with the data at CE4 after the Go=0.
The use of the array achieved this.

Thanks for the response
Paul
 
Last edited:
Upvote 0

Forum statistics

Threads
1,196,181
Messages
6,013,909
Members
441,794
Latest member
Deepakjaat31

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top