General discussion

Locked

Automatic Update of Excel Info

By danielle.schendzielos ·
I want to set up a process where when Workbook "A" is opened it goes out to Workbooks "B", "C" & "D", copies the information from certian sheets and pastes that info into sheets already created in Workbook "A". I tried recording a Macro but get "Run-Time Error 9 Subscript Out of Range".

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Automatic Update of Excel Info

by Lo In reply to Automatic Update of Excel ...

Hi

At the point you want data from another workbook, enter =[<workbook&gt<sheet>!<cell>
where:
<workbook> is the workbook name, in your case B, C or D. If the file is not in the same directory as your default, you will get a popup window that will let you locate. If there are multiple sheets you will get a sheet select window.

<sheet> is the name of the sheet

<cell> is the cell reference

To get A5 of Workbook B in Cell A1 of Workbook A, enter "=Sheet1!A5" (without the ").

You can also do formulas, such as =SUM(Sheet5!g5:g15)

Doing this will 'copy' the data, except that if the source (data in Workbook B, C, D changes, the change will be reflected in Workbook A when opened.

Hope that helps, lo

Collapse -

Automatic Update of Excel Info

This would work if I was only wanting a cell or two of information. I am wanting the whole spreadsheet and am not about to do this in the thousands of different cells involved. The formula also only works with numbers. I am needing to bring over sentances. Good answers, but not what I'm needing.

Collapse -

Automatic Update of Excel Info

by tclere In reply to Automatic Update of Excel ...

In workbook A, add a module and include this subroutine:

This will run when the workbook is opened, assuming that the user "Enables Macros".

Sub Auto_Open()
Dim wbA As Workbook, wsA As Worksheet
Dim wbB As Workbook, wsB As Worksheet
'Workbook A
Set wbA = ThisWorkbook
Set wsA = wbA.Sheets("Sheet1")

'Workbook B
Set wbB = Workbooks.Open(FileName:="C:\BookB.xls")
Set wsB = Sheets("Sheet1")
wsB.Range("A1").FormulaR1C1 = wsA.Range("A1").Text
wbB.Save
wbB.Close

End Sub

This should be enough to get you headed in the right direction. Hope it helps!

Collapse -

Automatic Update of Excel Info

It's somewhat helpful, but it only changed cell A1, not the entire sheet. When I tried to change the range so it would bring everything over it didn't do a thing to Workbook A, and it left the sheet in Workbook B blank. I'm not sure why it did that, but I need the entire sheet, not just a cell.

Collapse -

Automatic Update of Excel Info

I would like to make a correction of my answer to Lo. I played around some with the formula that you gave me. If you leave out the "Sum" and the "$" it brings over the words and works for what I need it to do. I wish I hadn't been so quick to reject your answer and could give you tech points.

Collapse -

Automatic Update of Excel Info

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums