Copy/Paste data to other existing workbook on timer

Question

I currently have this VBA -

Sub StartTimer()

Application.OnTime Now + TimeValue("00:00:15"), "AutoCalc"

End Sub

Sub AutoCalc()

Application.CalculateFull

Sheets("Kine").Range("B603:E603").Copy _
Destination:=Workbooks("AutoImportAverages.xlsx").Worksheets("AvgKine").Range("B1:E1")

Application.OnTime Now + TimeValue("00:00:15"), "AutoCalc"

End Sub

The .OnTime command is working perfectly without the Copy/Paste section, which is great.

This gives me a list of values from an SQL query that will auto-update, as well as an average at the bottom of each column of values.

I'm trying to set this up so that the average will automatically be added onto columns in Minitab but I believe that the Macro is stopping the Auto-Update in Minitab.

So my idea is to copy-paste the averages into an Excel Workbook that has no macros of its own and then link that to Minitab.

Have I put in the Copy-Paste code wrong or is there some issue with where the macros need to be stored and how?

Quick Edit - I should add that current code gives "Run-Time Error 9, Subscript out of range" and highlights copy/paste code.

You need to have the other Excel file open for this to work, check [How to copy data from another workbook (excel)?](http://stackoverflow.com/questions/482717/how-to-copy-data-from-another-workbook-excel) for the same problem, and a solution. — agold, Oct 06 '15 at 12:36
I'm exploring answers in your posted thread but it doesn't seem to be the same issue. Both Excel files remain open. I've tried setting up the cells as an External Reference in the new Workbook, but i think there's an issue due to the values being updated via a macro. This is why I'm trying to figure out the Copy method, just not sure why it won't work. — Jamsandwich, Oct 06 '15 at 13:33
You should be able to paste into a variety of different file formats, but you need to address it correctly in your VBA, i.e. you can't expect it to paste into `AutoImportAverages.xlsx` if the workbook is actually called `AutoImportAverages.xls`. — CactusCake, Oct 06 '15 at 13:52
I'm definately using the correct extension. I've even tried renaming the Workbook. — Jamsandwich, Oct 06 '15 at 14:09

score 3 · Accepted Answer · answered Oct 07 '15 at 08:44

I've found the solution.

My destination workbook was open in a separate window so the source wasn't recognising it as being open. Bit of a nightmare!

It's necessary to have both workbooks open in the same instance of Excel.

Additionally, my original paste code only pasted "#REF". I have changed that to -

Workbooks("AutoImportAverages.xlsx").Worksheets("AvgKine").Range("B1:E1").PasteSpecial xlValues

Works much better.

One more thing, in case anybody might find it useful. The source workbook must be active in order to carry out the auto-update.

Adding below line sorted out most issues though its still a work in progress -

ThisWorkbook.Activate

Copy/Paste data to other existing workbook on timer

1 Answers1