If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Referencing Cells in Multiple Worksheets
We have several workbooks with greater than 100 worksheets and need to
reference several cells from each worksheet to either a new worksheet/workbook to create a new table. The only thing I can think of is linking, but this just doesn't seem like an efficient way to do this. Is there a way that doesn't involve using Visual Basic or some type of programming? I am assuming that programming is the only way that this can be accomplished. Any input would be much appreciated! |
#2
|
|||
|
|||
Referencing Cells in Multiple Worksheets
Just some food for thought:
Linking will work, of course. But the issue there is that the linked workbooks either must be available or you would need to break the links at some point. If those workbooks get moved, then you end up with broken links to deal with. And if the contents of those other workbooks will not change in the future, it's almost a waste to take up the system resources in keeping the links. There is one other way to do it without VBA: instead of linking to the other workbooks and their sheets, you can REALLY get labor intensive and copy from the other books/sheets and paste into the new workbook. Other than linking or copy'n'paste, there's not a way to do it without VBA/macro code. But a VBA solution offers some advantages: once the job is done, you can delete the macro to shrink the size of the end workbook slightly, if you anticipate having to do this type of thing again in the future, you can keep a copy of the code around to use at that future time and not have to rewrite it. The VBA code to do such a thing really isn't that difficult to come up with: if you put all of the workbooks into a single folder for the task, it becomes much easier, even if it's just copies of them for this stage of the project. At that point, all that is needed to have is a 'map' of source data and that data's destination. What that would be is a list of the worksheets in the other workbooks and the cells on each that need to be copied, along with the sheet name and destination cell addresses for the data. In some cases you can 'shorthand' it, as: source Sheet1 A1:B18 needs to go to dest. Sheet1 beginning at H1 - that would tell the coder that 2 columns and 18 rows need to be copied and would end up at H1:I18. "RS" wrote: We have several workbooks with greater than 100 worksheets and need to reference several cells from each worksheet to either a new worksheet/workbook to create a new table. The only thing I can think of is linking, but this just doesn't seem like an efficient way to do this. Is there a way that doesn't involve using Visual Basic or some type of programming? I am assuming that programming is the only way that this can be accomplished. Any input would be much appreciated! |
#3
|
|||
|
|||
Referencing Cells in Multiple Worksheets
I sincerely appreciate your input. It was very helpful. Thank you so much!
RS "JLatham" wrote: Just some food for thought: Linking will work, of course. But the issue there is that the linked workbooks either must be available or you would need to break the links at some point. If those workbooks get moved, then you end up with broken links to deal with. And if the contents of those other workbooks will not change in the future, it's almost a waste to take up the system resources in keeping the links. There is one other way to do it without VBA: instead of linking to the other workbooks and their sheets, you can REALLY get labor intensive and copy from the other books/sheets and paste into the new workbook. Other than linking or copy'n'paste, there's not a way to do it without VBA/macro code. But a VBA solution offers some advantages: once the job is done, you can delete the macro to shrink the size of the end workbook slightly, if you anticipate having to do this type of thing again in the future, you can keep a copy of the code around to use at that future time and not have to rewrite it. The VBA code to do such a thing really isn't that difficult to come up with: if you put all of the workbooks into a single folder for the task, it becomes much easier, even if it's just copies of them for this stage of the project. At that point, all that is needed to have is a 'map' of source data and that data's destination. What that would be is a list of the worksheets in the other workbooks and the cells on each that need to be copied, along with the sheet name and destination cell addresses for the data. In some cases you can 'shorthand' it, as: source Sheet1 A1:B18 needs to go to dest. Sheet1 beginning at H1 - that would tell the coder that 2 columns and 18 rows need to be copied and would end up at H1:I18. "RS" wrote: We have several workbooks with greater than 100 worksheets and need to reference several cells from each worksheet to either a new worksheet/workbook to create a new table. The only thing I can think of is linking, but this just doesn't seem like an efficient way to do this. Is there a way that doesn't involve using Visual Basic or some type of programming? I am assuming that programming is the only way that this can be accomplished. Any input would be much appreciated! |
Thread Tools | |
Display Modes | |
|
|