Append workbook into a "Master" workbook

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

Ron,

How do I clear all cells on the first sheet except the first row?

Thanks,
Barb

 

[Ron de Bruin]

Hi Barb

basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear

 

[Guest]

Thanks. This gets me where I wanted to be today. I may have more questions
when I come back to this.

Barb

 

[Guest]

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]

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

 

[Guest]

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]

Hi Barb

Sorry, don't have experience with that