alliswell
Board Regular
- Joined
- Mar 16, 2020
- Messages
- 190
- Office Version
- 2007
- Platform
- Windows
- Mobile
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
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