Avoid Copy PasteValue statments

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
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:

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Deleted
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,582
Members
430,557
Latest member
MK15

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
Top