Referencing worksheets

[Guest]

I have a spreadsheet that is my main working spreadsheet but I need the same
date to be displayed in another workbook. How can I update the working
spreadsheet so as to have the same inforamtion updated in antoher workbook.

Curently I am updating both spreadsheets but would like to be able to only
update the working copy and it have the other one updated at the same time.
Is this possible, if so would appreicate the assistance.

Thanks

 

[Guest]

what you could do is to have a list of workbooks in your "data workbook"
then run a macro at closing the workbook:

go into the visual basic workbook of your data-workbook.
double click on "this workbook"
change to "workbook" in the topline at the left of this sheet and at the
right of the sheet choose BeforeClose
then put this macro in:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'this macro will look for workbook names in the first column
'of sheet "MyWorkbooks", opens that workbook, updates it and closes it
'this macro will auto run before the workbook is closed and is activated
'as soon as you close the data-workbook
'
Dim LastCell As Integer, I As Integer

Sheet("MyWorkbooks").Select
For I = 1 To LastCell
If Cells(LastCell, 1) <> "" Then
Workbooks.Open (Cells(LastCell, 1))
ActiveWorkbook.UpdateLink 'updates all links in the active
Workbook
ActiveWorkbook.Close
'if you have links in between the workbooks, you better put the
'CLOSE command after the NEXT command!
Else
Exit Sub
End If
Next I
End Sub

it only (auto)runs in the main workbook, marked in the Project overview as
an excelsheet.

good luck!

 

[Guest]

of course you need to give LastCell a value before the for...next -loop!
I forgot, sorry.

WJvR

 

[Guest]

I played a bit with it my self this time and found some problems. here is a
working one:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'this macro will look for workbook names in the first column
'of sheet "MyWorkbooks", opens that workbook, updates it and closes it
'this macro will auto run before the workbook is closed and is activated
'as soon as you close the data-workbook
'the names of the worksheets that you want to update must have the full path
'like this: E:\Tables\testfiles\myFile_01.xls
'the first worksheet starts in cell A1
'in this example the last worksheet is in cell A10 (LastCell=10)
' WJvR 20060901
'
Dim LastCell As Integer, I As Integer
LastCell = 10
Sheets("MyWorkbooks").Select
For I = 1 To LastCell
If Cells(I, 1) <> "" Then
Workbooks.Open (Cells(I, 1))
ActiveWorkbook.RefreshAll 'updates all links in the active
Workbook

'if you have links in between the workbooks, you better put the
'CLOSE command after the NEXT command!
Else
Exit Sub
End If
Next I
ActiveWorkbook.Close savechanges:=True
End Sub 'before close

Good luck again!
WJvanRooijen

 

[Guest]

You have lost me, sorry not familiar with Visual basic. Where do I go? The
name of the Data workbook is "Tangible Capital Assets" and there are a number
of sheets in that work book. The other workbook I want updated is called
"2007-08 Capital Submission" and the worksheet in that book I want updated is
called "Cap 11 -Tangible Capital Assets"

Does this help in you helping me iwth my dilemma?

Thanks

 

[Guest]

ok, you need to open the visual basic editor.
send me an empty email to my address:
(e-mail address removed)

I will reply on that with my example for you!

Wim-Jan van Rooijen