Avoid Copy PasteValue statments

[Paul Sansom]

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

[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]

 

[Fluff]

Is this what you mean?

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

 

[MARK858]

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

   With Sheet13.Range("AA26:AH43")
        Sheet2.Range("CE4").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[Paul Sansom]

Thanks Fluff..... that worked fine although I think you meant the target range was different
Array called BertData

    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

 

[Paul Sansom]

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

 

[MARK858]

Deleted