Future spreadsheets to auto update existing spreadsheet?

[knigget]

I have a spreadsheet that is used as a template - it is copied and pasted ALWAYS into a new a folder - it always has the same filename (job progress.xls). The data in these spreadsheets is always different but always laid out the same (column headings, row headings)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have another spreadsheet (summarysheet.xls) that contains snippets of information from the many jobprogress.xls that have been created over time. Any updates made in the jobprogress.xls successfully updates the summarysheet.xls by paste link.<o></o>
<o> </o>
I would like summarysheet.xls to update itself automatically..... If I create a newjobprogress.xls, I would like summarysheet to insert snippets onto a new line. Is this possible?

The title of this thread should probably read "Future spreadsheets that don't exist but one day will, to send data from certain cells to an existing spreadsheet that has been there for ages?"!
<o> </o>
My take on this is that summarysheet.xls cannot be coded to look for contents of another workbook that doesn't yet exist. But I would expect that jobprogress.xls CAN be coded to send information to summarysheet.xls as summarysheet is a constant (it’s always there).<o></o>

What do you reckon?

 

[jbeaucaire]

Assuming your template can have macros in it:

1) Place a formula in a cell that is designed to lookup the key identifying piece of information from this template in your summary. This formula will, of course, have no result or an error result when you first start a new template.

2) Use a Workbook_BeforeSave() macro in the template to:

a) stop the regular save command
b) save the template in the correct new folder and filename

The formula linking to the Summary sheet should now be able to function properly, but should still have no good result since this template is not yet listed in the summary, so:

c) Open the summary file (if it's not already open)
d) Add the formulas to link this new template to the summary in the next empty row on the correct sheet
e) Verify the template now has a proper result in the test cell
f) Close the summary sheet if it wasn't open originally
g) Resave the template again to update the changes.

 

[knigget]

Assuming your template can have macros in it:

1) Place a formula in a cell that is designed to lookup the key identifying piece of information from this template in your summary. This formula will, of course, have no result or an error result when you first start a new template.

Hi Jerry and thanks for your reply..

Whilst not a total newbie to Excel, I have never used it to this extent before, hence my next question/s!:

I am using the Excel 2003 Office help tute with this and am using the VLOOKUP command. For the purpose of this task, I am looking at sending 3355 to my summary sheet? Your step 1 is telling me to put the VLOOKUP in the 'jobprogress.xls'?

Also, I did simplify my brief at the start of this thread and as such I believe I can leave the macro save part out. Basically, I have a template folder that users will copy and rename to the project number - inside this folder is everything they will need to complete a project - Word document templates, sub folders for their drawings etc and the 'jobprogress.xls'.


Will your method truly automate everything or will the summary sheet need formulas adding everytime to pull the data from the many jobprogress.xls that will be created in the future?

Thanks for your help so far

 

[knigget]

Ok - I have managed to find a macro that does what I want. This macro is in the 'jobprogress' workbook:

'START COPY TO SUMMARY SHEET
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim DestWB As Workbook
    Dim DestSh As Worksheet
    Dim Lr As Long
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Change the file name (2*) and the path/file name to your file
    If bIsBookOpen_RB("summary.xls") Then
        Set DestWB = Workbooks("summary.xls")
    Else
        Set DestWB = Workbooks.Open("C:\Documents and Settings\user\Desktop\TEST\excel test\proj1\summary.xls")
    End If
    'Change the Source Sheet and range
    Set SourceRange = ThisWorkbook.Sheets("Sheet1").Range("A3:Z3")
    'Change the sheet name of the database workbook
    Set DestSh = DestWB.Worksheets("Sheet1")
 
    Lr = LastRow(DestSh)
    Set DestRange = DestSh.Range("A" & Lr + 1)
    'We make DestRange the same size as SourceRange and use the Value
    'property to give DestRange the same values
    With SourceRange
        Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
    End With
    DestRange.Value = SourceRange.Value
    DestWB.Close savechanges:=True
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
'END COPY TO SUMMARY SHEET
End Function

This would work a treat if it weren't for two things:

1) If I run the macro, it won't save to the 'summary' workbook unless it's open. When it is open it closes it (to save it). Why does 'summary' have to be open when it closes it anyway?

2) The macro adds the selected data to the next new line in 'summary' workbork. However, in the 'job progress' workbook, for my purposes A3 is a project number. Everytime this is updated, the 'summary' workbook has multiple entries of the same project number. Could I get 'summary' to display only the latest project number data? Something like
"before sending data, check if same project number exists in 'summary', if it does then delete that row and insert new data"?!

Thanks in advance

 

[jbeaucaire]

With a new boolean variable you can remember if the Summary was open or not and react accordingly at the end.

By doing a .FIND on your value you can attempt to set the DestRange to the existing row if the project number is already in column A somewhere, thus that row would get updated.

Option Explicit

'START COPY TO SUMMARY SHEET
    Dim SourceRange As Range
    Dim DestRange   As Range
    Dim DestWB      As Workbook
    Dim DestSh      As Worksheet
    Dim LR          As Long
    Dim WasOpen     As Boolean
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 'Change the file name (2*) and the path/file name to your file
    WasOpen = bIsBookOpen_RB("summary.xls")
    If WasOpen Then
        Set DestWB = Workbooks("summary.xls")
    Else
        Set DestWB = Workbooks.Open("C:\Documents and Settings\user\Desktop\TEST\excel test\proj1\summary.xls")
    End If
    
 'Change the Source Sheet and range
    Set SourceRange = ThisWorkbook.Sheets("Sheet1").Range("A3:Z3")
 
 'Change the sheet name of the database workbook
    Set DestSh = DestWB.Worksheets("Sheet1")
    LR = LastRow(DestSh)
    On Error Resume Next
    Set DestRange = DestSh.Range("A:A").Find(SourceRange.Cells(1, 1), LookIn:=xlValue, LookAt:=xlWhole)
    If DestRange Is Nothing Then Set DestRange = DestSh.Range("A" & LR + 1)
  
  'We make DestRange the same size as SourceRange and use the Value
  'property to give DestRange the same values
    With SourceRange
        Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
    End With
    DestRange.Value = SourceRange.Value
    
    If WasOpen Then
        DestWB.Save
    Else
        DestWB.Close True
    End If
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
'END COPY TO SUMMARY SHEET

 

[knigget]

With a new boolean variable you can remember if the Summary was open or not and react accordingly at the end.

By doing a .FIND on your value you can attempt to set the DestRange to the existing row if the project number is already in column A somewhere, thus that row would get updated.

Option Explicit
 
[/QUOTE]
 
I'm sorry - I do require spoon feeding as this is blowing my mind!
 
Thanks for the reply though

 

[jbeaucaire]

Are you asking a followup question?

The .FIND method is attempting to find the project number in the existing data. If the .FIND resolves to an actual cell, then the macro will use that existing row as your DestRange, so the existing row of data will get updated.

If the FIND fails to find anything, then the DestRange will be declared as the "next empty row in the database" instead.

Ok?