Running code in one open workbook another active

**shumba** · 06-13-2010, 06:18 AM

Hi,
I have an open active workbook (called Data Collection) collecting real time data via an add in from an internet server.Another workbook (called Todays Workbook) is also open and has linked cells to Data Collection.

When I activate Todays Workbook it stops the code from running in Data Collection. Is it possible to have Todays Workbook activated while still running the code in Data Collection.

Being very new to VBA. Help would be much appreciated. Thank you.

**royUK** · 06-13-2010, 06:37 AM

Not sure why you have cells in a workbook linked to an addin.

Post the code or attach the workbooks

**shumba** · 06-13-2010, 10:01 PM

Originally Posted by royUK

Not sure why you have cells in a workbook linked to an addin.

Post the code or attach the workbooks

RoyUK,

Thank you for your time. Here is the code.

Private RunWhen As Double
Private Const cRunIntervalSeconds1 = 2
'Public Const cRunWhat = "Timed" 'he name of the procedure to run
Private Const cRunWhat1 = "Timer"
Sub StartTime()
    Application.OnTime TimeValue("08:00:00"), "Initial"
    Application.OnTime TimeValue("08:00:02"), "Timer"
End Sub
Sub StartTimer2()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds1)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat1, _
        Schedule:=True
End Sub
Sub Initial()
    'Volume
    Sheets("T Volume").Select
    Range("c2:c1560").Copy
    Range("f2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Price
    Sheets("Price").Select
    Range("d2:d1560").Copy
    Range("f2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'VWAP
    Sheets("VWAP").Select
    Range("d2:d1560").Copy
    Range("f2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Bid
    Sheets("Bid").Select
    Range("d2:d1560").Copy
    Range("f2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Ask
    Sheets("Ask").Select
    Range("d2:d1560").Copy
    Range("f2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Bid Volume
    Sheets("Bid Volume").Select
    Range("c2:c1560").Copy
    Range("d2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Ask Volume
    Sheets("Ask Volume").Select
    Range("c2:c1560").Copy
    Range("d2").Activate
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
End Sub

Sub Timer()
    'Volume
    Sheets("T Volume").Select
    Range("c2:c1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Price
    Sheets("Price").Select
    Range("d2:d1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'VWAP
    Sheets("VWAP").Select
    Range("d2:d1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Bid
    Sheets("Bid").Select
    Range("d2:d1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Ask
    Sheets("Ask").Select
    Range("d2:d1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Bid Volume
    Sheets("Bid Volume").Select
    Range("c2:c1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    'Ask Volume
    Sheets("Ask Volume").Select
    Range("c2:c1560").Copy
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Activate
    StartTimer2
End Sub

This code is for the Data Collection workbook and runs fine when this workbook is activated. However when Today's workbook is activated the the code stops at

Sub Timer()
    'Volume
    Sheets("T Volume").Select

**shumba** · 06-14-2010, 04:20 AM

I have added two lines of code at the beginning of the Sub Timer Procedure.

Application.ScreenUpdating = False
    Workbooks("Data Collection Template Trial 3.xlsm").Activate

and one line at the end before StartTimer2

Workbooks("Todays Template ver 1.xlsx").Activate

Thanks to all those who have read and replied.

Running code in one open workbook another active

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Running code in one open workbook another active

Re: Running code in one open workbook another active

Re: Running code in one open workbook another active

Re: Running code in one open workbook another active

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions