Temporarily suspend refresh

[gizzard]

I want to trigger a macro at a specific time that opens another spreadsheet in the same instance. This newly opened spreadsheet will then have information passed to the one linked to BA.

This causes problems with the maco being run against BA and I get conflicts beween the spreadsheets.

My idea was to temporarily suspend or extend the refresh time just before opening the second spreadsheet so that the time is long enough for the processing I want to carry out to proceed.

Is there another way around this problem of conflicting spreadsheets other than changing the refresh rate to, say, 1000 seconds?

[alrodopial]

Are you specific in your code?
ie
ThisWorkbook.Sheets(".......").Range("A1").Copy

[gizzard]

I'm sorry but I don't quite understand your question but ,yes, I could use code similar to your suggestion.

[alrodopial]

I get conflicts beween the spreadsheets.

You shouldn't get conflicts if your code is ok
Can you post the part of the code that opens the workbook and passes the data?

[gizzard]

I appreciate your help with this. Unfortuately I have since adapted the code that didn't work but this is still a similar base for it that I'm now using with some success.
This is the outline code that is kicked off at a specific time in the spreadsheet that is running against BA:
I know that it's over the top in switching things off and then back on but I was trying everything to stop the conflicts!

Sub GetData()

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Run the macro that opens the other spreadsheet and determines data to be copied and pasted to this spreadsheet

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

[osknows]

See if this helps

Code: Select all

Option Explicit

Sub GetData()
Dim wbObj As Workbook
Dim wsObj As Worksheet
Dim wbPath As String
Dim wsName As String

   'path of worknook to copy to with sheet name
    wbPath = "C:\Users\username\Desktop\WorkBooktoPasteInto.xlsm"
    wsName = "SheetXYZ"
   
   
    Set wbObj = GetObject(wbPath)
    With wbObj
       
        'NOTE: Sheet1 is codename of sheet you want to copy from in ThisWorkbook
        .Sheets(wsName).Range("A1:Z100").Value = Sheet1.Range("A1:Z100").Value
       
'        .Windows(1).Visible = True: .Save  'make visible before save
'        .Close
       
    End With
    Set wbObj = Nothing

End Sub

This assumes the data you want to copy is in Sheet1.Range("A1:Z100")
.Save & .Close are optional as you may not want to save and close each time

[gizzard]

Thanks for that.
My VBA skills are fairly limited but from what I can see instead of copying and pasting you are suggesting to make a range in the spreadsheet linked to BA equal to a range in the other spreadsheet.

I'm assuming that I still keep all my disabling/enabling code for the various Application parameters and just call a maco like the one that you suggest between the diable..enable code.

[osknows]

Yes, it avoids using the clipboard altogether so is faster - the only minor drawback is that it only copies values rather than cell formats.

If you're only copying a range once each time and it's quick then I would recommend removing everything except Application.EnableEvents. It could be the case that turning off/on the others could take longer than the operation itself.

[gizzard]

Thanks, I'll certainly give it a go.