Linking to Summary Sheet Changing Range

[Cathy]

I’m using Excel 2003. I have about 9 different workbooks for tracking
separate types of projects and tasks. Each workbook contains a worksheet
with information specific to that project including individual tasks, costs
associated with each task, dates, etc. I have a starting balance for each
project and as each task is created, an associated dollar amount is entered
and draws down on the available balance. Therefore, rows (new tasks) are
constantly being added which creates a constantly changing balance.

I also have a summary workbook that takes a couple of the pertinent columns
from the 9 project workbooks, including the current balance for each one, to
give an up-to-date snapshot of each project. The problem is that since the
rows are always being added, my hyperlinks are constantly changing. The
current balance does not, of course, reside in the same cell in each
workbook. Is there a way (maybe code) to automatically have that summary
sheet take specified columns from a constantly changing range and insert in
the summary sheet? I’d really like to have it do the current balance and
some date columns.

Any help on this is appreciated, thanks.

 

[john]

Cathy,
If all your workbooks are placed an in shared drive, you can read data using
formula without opening them.

As an example, following code cycles through each workbook in listed
directory - copies Data from Range A1:A20 and places data in Columns in
sheet 1

The drawback to this approach is that you will need to hard code the Range
but you can guess & make it larger and just delete any returned zero’s.

Have a play & see if any help.

Place code in standard module.


Sub SummariseData()
Dim strFile
Dim strFolder
Dim mydata As String

'Folder where your workbooks are located
' change as required
strFolder = "c:\Documents and Settings\"

strFile = Dir(strFolder & "*.*", vbNormal)

Application.ScreenUpdating = False

i = 1
Do While strFile <> ""

'data location & range to copy
'change sheet name & range as required
mydata = "='" & strFolder & "[" & strFile & "]Sheet1'!A1:A20"
'link to worksheet
With ThisWorkbook.Worksheets(1)

'this range must match mydata range
'change as required but only the numeric values
'i is a variable and is used to increment col selection
With .Range(.Cells(1, i), .Cells(20, i))
.Formula = mydata


'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With

Application.CutCopyMode = False

strFile = Dir

i = i + 1

Loop

Application.ScreenUpdating = True
End Sub

 

[ryguy7272]

Maybe one of these:
http://www.rondebruin.nl/summary2.htm
http://www.rondebruin.nl/copy7.htm

If not, look here:
http://www.rondebruin.nl/tips.htm

Lots of copy/paste/merge examples there! Ron has covered almost every
possible scenario in this topic.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Cathy,
If all your workbooks are placed an in shared drive, you can read data using
formula without opening them.

As an example, following code cycles through each workbook in listed
directory - copies Data from Range A1:A20 and places data in Columns in
sheet 1

The drawback to this approach is that you will need to hard code the Range
but you can guess & make it larger and just delete any returned zero’s.

Have a play & see if any help.

Place code in standard module.


Sub SummariseData()
Dim strFile
Dim strFolder
Dim mydata As String

'Folder where your workbooks are located
' change as required
strFolder = "c:\Documents and Settings\"

strFile = Dir(strFolder & "*.*", vbNormal)

Application.ScreenUpdating = False

i = 1
Do While strFile <> ""

'data location & range to copy
'change sheet name & range as required
mydata = "='" & strFolder & "[" & strFile & "]Sheet1'!A1:A20"
'link to worksheet
With ThisWorkbook.Worksheets(1)

'this range must match mydata range
'change as required but only the numeric values
'i is a variable and is used to increment col selection
With .Range(.Cells(1, i), .Cells(20, i))
.Formula = mydata


'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With

Application.CutCopyMode = False

strFile = Dir

i = i + 1

Loop

Application.ScreenUpdating = True
End Sub

--
jb

I’m using Excel 2003. I have about 9 different workbooks for tracking
separate types of projects and tasks. Each workbook contains a worksheet
with information specific to that project including individual tasks, costs
associated with each task, dates, etc. I have a starting balance for each
project and as each task is created, an associated dollar amount is entered
and draws down on the available balance. Therefore, rows (new tasks) are
constantly being added which creates a constantly changing balance.

I also have a summary workbook that takes a couple of the pertinent columns
from the 9 project workbooks, including the current balance for each one, to
give an up-to-date snapshot of each project. The problem is that since the
rows are always being added, my hyperlinks are constantly changing. The
current balance does not, of course, reside in the same cell in each
workbook. Is there a way (maybe code) to automatically have that summary
sheet take specified columns from a constantly changing range and insert in
the summary sheet? I’d really like to have it do the current balance and
some date columns.

Any help on this is appreciated, thanks.