Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to Summary Sheet Changing Range
Im 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? Id really like to have it do the current balance and some date columns. Any help on this is appreciated, thanks. -- Cathy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to Summary Sheet Changing Range
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 zeros. 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 "Cathy" wrote: Im 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? Id really like to have it do the current balance and some date columns. Any help on this is appreciated, thanks. -- Cathy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to Summary Sheet Changing Range
Maybe one of these:
http://www.rondebruin.nl/summary2.htm http://www.rondebruin.nl/copy7.htm If not, look he 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''. "john" wrote: 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 zeros. 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 "Cathy" wrote: Im 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? Id really like to have it do the current balance and some date columns. Any help on this is appreciated, thanks. -- Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking multiple worksheets to one summary sheet | Excel Discussion (Misc queries) | |||
LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS | New Users to Excel | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |