Excel 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"))
This creates hyperlinked EDIT text automatically in sheet2 in column E after each invoice gets saved.
Now in sheet2 when i click on this EDIT text, that particular invoice gets open. That are saved in C:\Invoices
Now after editing that invoice i save it by Ctrl+S it gets saved but only in that particular invoice but not in the database which i created, it doesnt gets updated. So i want to add something in above macro such code that when i click on EDIT button the invoice shud get opened but with some HYPERLINKED TEXT LIKE UPDATE so that when i click on it my invoice shud also get saved and also in the database sheet sheet2 it shud get updated.

Thanks to everyone

 

[offthelip]

I am not really clear about what you are doing but if you want to be able to update individual invoices and the updates be reflected back into your master workbook, then the only way to do that is to link them together. You can do this using vBa. I have just used a simplified version of your code, which copied sheet1 and saves it as a new workbook with the name in sheet 1 b1 and then it links B1 and H1 on sheet 1 of this new workbook back to columns B and C of sheet 2 of the orginal in a row given by "rw" ( Set to 4 in my test version)

Sub test()
rw = 4  ' just for testing
Dim sh1 As Worksheet, sh2 As Worksheet, spath As String
Dim sName As String, rr As Range
Dim sOld As String, btn As Button
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
spath = "C:\Invoices\"
sName = sh1.Range("B1").Text & ".xls"
Aname = ActiveWorkbook.Name
Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:=spath & sName
Range("B1").Select
            Selection.Copy
Windows(Aname).Activate
             Worksheets("Sheet2").Select
             Cells(rw, "B").Select
             ActiveSheet.Paste Link:=True
Windows(sName).Activate
             Worksheets("Sheet1").Select
             Range("H1").Select
             Selection.Copy
Windows(Aname).Activate
             Worksheets("Sheet2").Select
             Cells(rw, "C").Select
             ActiveSheet.Paste Link:=True
End Sub