Database Updating

[alliswell]

Hi everyone !
I created macro to create invoice and get it saved with the name as invoice no in C\Invoices. For ex. C\Invoices\0001.xlsm.
Invoice template is in sheet1 with SAVE command button. After filling invoice when i hit save button invoice get saved in C:\invoice and in sheet2 database of each invoice gets saved with hyperlinked EDIT in column E of each database.
For example in sheet2
A B C D E
1 INVOICE NAME QTY. TOTAL
2 0001 SAM 4 600 EDIT
3 0002 JAN 7 790 EDIT

NOW when i click on hyperlinked edit in E2 0001 invoice gets opened and i make some changes in it and saves it and closes it. I changed QTY from 4 to 5 and Total becomes 750 but it didnt get updated in sheet2 database. Please can you help linking database to invoices to get updated if needed to. Thanks.
Here is my macro.

 

[alliswell]

Option Explicit
Sub Button_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, spath As String
Dim sName As String, rw As Long, rr As Range
Dim sOld As String, btn As Button
Set sh1= Worksheets("Sheet1")
Set sh2= Worksheets("Sheet2")
rw=sh2.Cells(sh2.Rows.Count,1).End(xlUp).Row+1
Set rr=sh2.Cells(rw,1).Resize(1,4)
Do While Application.CountA(rr)<>0
Set rr=rr.Offset(1,0)
Loop
rw=rr.Row
spath="C:\Invoices\"
sName=sh1.Range("B1").Text & ".xls"
sh2.Cells(rw,"B").Value=sh1.Range("B1").Value
sh1.Cells(1,"E").Copy sh2.Cells(rw,"A")
sh2.Cells(rw,"C").Value=sh1.Range("H1").Value
sh2.Cells(rw,"D").Value=sh1.Range("e11").Value
Application.DisplayAlerts=False
ThisWorkbook.SaveAs spath & sName, FileFormat:=xlExcel8
sOld=spath & sName
Application.DisplayAlerts=True
sh1.Range("B1").Value=sh1.Range("B1").Value+1
sh1.Range("E1,H1,A5:D10").ClearContents
sName=sh1.Range("B1").Text & ".xls"
Application.DisplayAlerts=False
ThisWorkbook.SaveAs spath & sName, FileFormat:=xlExcel8
Application.DisplayAlerts=True
Workbooks.Open (sOld)
For Each btn In ActiveSheet.Buttons
btn.Delete
Next
ActiveWorkbook.Close SaveChanges:=True
End Sub

In sheet2 E2 i put this formula to edit the invoice in sheet2 database, if required.
=If(B2="","",HYPERLINK("C\Invoices\"&Text(B2,"0000")&".xls","Edit"))

 

[alliswell]

Still nobody replied for solution.....

 

[alliswell]

Please somebody reply for above solution...

 

[dmt32]

Hi,

Reason probably not getting any responses could be due to you marking your own posts as solution – maybe

With regard to your issue, I suspect your problem is that when you open a workbook via one of your hyperlinks edit & save changes, reason changes not showing in your database workbook is because you have not created any coding to perform this task?

As a suggestion, rather than saving copies of each invoice as separate workbook consider creating one master workbook (database) to post the record from your invoice template to.

You would then find it much easier to call back a record to your invoice template for editing from the master workbook.



Dave