A masterfile for all indefinite progress reports

[melvinnreyy]

Hi,


Levels 1-10, I have 4 with regards of knowledge in using Excel. I'm working on an office and I'm trying to organize all my reports for my ease. I want to have a MASTER FILE for my reports in egnineering which will I name as "MasterFile".

MasterFile consists of:
(all with date and quantity ofcourse)
1. Pulled cables
2. Trays
3. Instruments installed

I am submitting reports per week through a new sheet say "Nov1-7". So what happens is I just copy "Nov1-7" and rename it to "Nov8-14" for next week's report after I submit "Nov1-7".

Nov1-7 to indefinite number of sheets consist of:
(the same as "MasterFile's")

What I want to do is to update Nov1-7, Nov8-14, Nov14-20, ...etc, and should show automatically in the MasterFile without even opening it. Means updating directly. Is this possible considering there's an indefinite number of progress reports which are added continuously by weekly basis?

I hope for your help. Thanks very much and super more power to all of you.

 

[JLGWhiz]

You don't provide any information about the sheet layouts and formats, but assuming that the MasterFile is just a copy of the weekly reports and the sheet headers are all the same in the reports and the master file, then you could create a Forms Control button on the report sheet, or a keyboard shortcut key, to run the macro below. The macro would open the MasterFile copy the data from the report and save and close the MasterFile. The MasterFile must be opened so that the data can be entered and saved, but it only takes a couple of seconds when done by code. This procdure assumes that code will be run from the workbook containing the weekly reports and that MasterFile is in the same directory.

Sub updateMastFile()
Dim wb As Workbook, sh As Worksheet, fPath As String, ssh
fPath = ThisWorkbook.Path
Set ssh = ActiveSheet
Set wb = Workbooks.Open(fPath & "MasterFile.xlsx")
Set sh = wb.Sheets(1) 'Edit sheet name
ssh.Range("A2", ssh.Cells(Columns.Count, 1).End(xlUp)).EntireRow.Copy sh.Cells(Columns.Count, 1).End(xlUp)(2)
wb.Close True
MsgBox "Workbook " & wb.Name & " is updated and saved."
End Sub

To use this procedure, copy it to the standard code module 1. Close the VB editor and if the workbook is not already macro enabled, save the workbook as a macro enabled workbook (.xlsm). Then either assign the macro to a forms control button, or a keyboard shortcut key, or both. To assign it to a button, click 'Developer' on the ribbon, then click 'Insert' and choose the button from the Form Controls panel. Click in the area on the screen where you want to place the button, then right click the button and click 'Assign Macro' from the pop up menu. Complete the process with the dialog box that appears. To assign a keyboard shortcut key, click Macros, then click Optiohns.
How it works: The code will run from the workbook containing the weekly report and the weekly report must be the active sheet at the time the macro is initialized. The procedure will open the workbook named MasterFile and copy form the weekly report all rows except row 1 headers and post them to the next available row in MasterFile. It will then close and save MasterFile and display a message confirming the completion of the procedure.

 

[melvinnreyy]

Can it be done without macro functions sir?

If I want my masterfile to be in different format but contains the same data as the indefinite files, what can I do?

Can you please show it by steps sir. Im really slow at excel. If you may...

Thanks for the help

 

[melvinnreyy]

Say this is the master file:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Size[/TD]
[TD]Total Quantity (m)[/TD]
[/TR]
[TR]
[TD]cables[/TD]
[TD]1Cx2.5
[/TD]
[TD]this to be automatically[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1Cx5.0[/TD]
[TD]filled by the indefinite[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3Cx2.5[/TD]
[TD]files[/TD]
[/TR]
[TR]
[TD]trays[/TD]
[TD]300mm[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]200mm[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]150mm[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
and so on.....

the indefinite files looks different, say like this: example january 1-7

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]size[/TD]
[TD]quantity[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cables[/TD]
[TD]1Cx2.5[/TD]
[TD]filled by me[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3Cx2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
different it the sense of the format (fonts, margins, borders, formulas, etc) but the same input in a different cell.


I want the master file to be untouched. Indefinite files are only updated through hand and automatically updated also in master file. Note indefinite files are continuous til the end of the project..

Is this possible sir?