| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Append workbook into a "Master" workbook
We are looking at having separate workbooks (with one worksheet) to track
time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#2
|
|||
|
|||
|
Hi Barb
I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#3
|
|||
|
|||
|
Ron,
It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#4
|
|||
|
|||
|
I also want to have a row of data at the beginning and have the "pasted" data
start in row 2. Am I asking for too much? :) Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#5
|
|||
|
|||
|
Hi Barb
Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#6
|
|||
|
|||
|
Ron,
How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#7
|
|||
|
|||
|
Hi Barb
basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#8
|
|||
|
|||
|
Thanks. This gets me where I wanted to be today. I may have more questions
when I come back to this. Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#9
|
|||
|
|||
|
Hi Barb
determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#10
|
|||
|
|||
|
Ron,
I've discovered that the "folder" containing the data may ultimately be in a password protected repository and not on a server. Can this still be done in that case? Thanks, Barb "Ron de Bruin" wrote: Hi Barb determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
|
#11
|
|||
|
|||
|
Hi Barb
Sorry, don't have experience with that -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, I've discovered that the "folder" containing the data may ultimately be in a password protected repository and not on a server. Can this still be done in that case? Thanks, Barb "Ron de Bruin" wrote: Hi Barb determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating master workbook from source that may/may not exist | Excel Worksheet Functions | |||
| Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) | |||
| Master Workbook used as my template? | New Users to Excel | |||
| Unprotect Workbook | Excel Discussion (Misc queries) | |||
| How do I set up a Workbook with a master compiled worksheet and o. | Excel Worksheet Functions | |||
Linear Mode
