asked on

how Do I get VBA to act on whatever Excel file i have open as the Object?

I used Excel to Record to get a start

But I get a "subscript out of range" error

I need the object to be whatever Excel file i have open, as the name changes, but the sheets and range locations are always the same.

I am trying to get the macro to extract that one cell (N61) data and copy/paste it into the next available cell in a column in another Workbook, where i am trying to create a linked table for Access.

ActiveWindow.ScrollWorkbookTabs Sheets:=-15
Sheets("Production Summary").Select
ActiveWindow.SmallScroll Down:=21
Range("N61").Select
Selection.Copy
Windows("Prodsumm_tbl.xlsx").Activate
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll Down:=1
Range("B65").Select
ActiveWindow.LargeScroll Down:=1
Range("B99").Select

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

ThisWorkBook will always refer to the WorkBook containing the code.
ActiveWorkBook will always refer to the workbook that is active.

If Sheets("Production Summary") is in the workbookwith the code then assuming that the destination sheet is open in the destination workbook it could be

ThisWorkbook.Sheets(1).Range("N61").Copy Workbooks("Prodsumm_tbl.xlsx").Range("B65")

Open in new window

Bill Prew

ActiverWorkbook and ActiveSheet can be used to refer to the currently active workbook and worksheet.

»bp

Norie

Oops, didn't notice the Copy.

Rex

ASKER

Thank you all very much