automate process

Here is my question:

Workbook1 contains 12 sheets for each month of the year, as well as a Summary sheet.

Each of the 12 monthly sheets are for hours worked per each day.

The summary sheet runs formulas to check each monthly sheet and reports total hours worked each month of each year.

Workbook1 and the Sheets in it are exact duplicates for each employee, to make the cell referencing and trouble shooting easier when its needed.

Workbook2 contains two sheets with a macro to duplicate workbook1 and rename it for each employee in column A of Sheet1 of Workbook2.

Now using Workbook2, Sheet2, reference the Summary Sheet of Workbook1 along a Row 1 for employee 1.

Row2 for employee 2

Row 3 for employee 3

and so on..


I have a working copy of this but what I am stuck on is with 100-200 employees, it's a real pain to manually edit each cell of each row to locate the data for each employee.

Is there a way to automate or somehow change Workbook2,Sheet2

B1='H:\Shifts\[BobD.xls]YEAR TOTAL'!$B$15
C1='H:\Shifts\[DanW.xls]YEAR TOTAL'!$B$15
D1='H:\Shifts\[BillyQ.xls]YEAR TOTAL'!$B$15
E1='H:\Shifts\[DaveM.xls]YEAR TOTAL'!$B$15
F1='H:\Shifts\[TonyR.xls]YEAR TOTAL'!$B$15
G1='H:\Shifts\[RussV.xls]YEAR TOTAL'!$B$15

to automatically select or change the code for the [name.xls] based on the existing names on Workbook2, Sheet1?

The entire Sheet2 contains hundreds of these formulas and I'm hoping to find a way to just enter the names in one column, and have the creation macro change the [name.xls] for each cell.

Anyone care to take a crack at this? I'd love an example to try to apply it to the rest of the sheet.

Thanks
Aurbo99

Hi

Try

Please Login or Register  to view this content.

Assumes that you are in sheet1, and that the names start in A2 and continue down the page.


rylo

This works great! thanks..

How would you get it to post in a column vice a row?

Aurbo99

Hi

swap (0,coloff)
to
(coloff,0)

To make it more indicative, I'd change coloff to rowoff as a variable name.


rylo

Aurbo

Not sure I follow. If you have 4 names in C1:C4, then you are replacing those 4 cells with a formula that points to E12 in each of the files.

Couldn't you then put a sum formula in C5 that sums C1:C4? Or is C supposed to be a progressive of previous names + current row name?


rylo

Thanks for the reply Rylo,

C is supposed to be progressive for this step,

I need to add all the values of each "='" & Filename & "[" & eName & ".xls]YEAR TOTAL'!$E$12" that next i finds and report it in ThisWorkbook.Sheets("MONTHLY REPORT").Range("E1").Select

Either a formula change to add each to the E1 cell formula or store the running total in a variable and just have it report in E1 after the macro is complete.

Hope that helps

Cheers
Aurbo

Aurbo

I'd probably perform the increment in the code, and just paste the output to the final place. That way the names in column C aren't changed and you don't have a heap of linkages in the file.


rylo