Hyperlink macro

[akiz009]

I don’t have a good knowledge in VBA scripting.
I want to create a workbook for my customers, there will a master sheet. The master sheet will contain the list of my customer with a unique serial number. There will be a “detailed sheet” for every customer. So that I can find a customer by serial number on Master Sheet and move to that customer’s details sheet by a hyperlink.

I have made a Macro button which creates “New Sheet” arranged by a “Sample Form” with asking sheet name with a pop-up window.
Now I need to add one more thing to that macro. I want that macro to put a Hyperlink automatically on my “master sheet” on Column “E” one by another while creating each New Sheet. The hyperlink name should be same as the newly created sheet’s name.


Thank you sir

 

[akiz009]

I am not educated in IT. But all the Experts here, are my teachers.
Thank You. Mr. Andrew & Mr. Alan

 

[akiz009]

Thank you :D

 

[akiz009]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then Target.Offset(0, -1).Value = Date

""" I used this code. When i write "Yes" on any cell on column "B", this code gives "Date at the same row on Column "A"....
But i am trying to modify the code in this way....

I want to target Column "D".
When i will write "Yes" on Column "D".
It will sum "column A" and Column B" at the same row,, And put the result on "column C" at the same raw.

I tried modifying in many ways. like mentioned below..

(( Dim rng As Range
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = "Membership" Then Target.Offset(0, -1).Value = "=sum(A:B)" )))))))))

BUT Failed. I want to learn it.
Thank You.....

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    If Target.Value = "Membership" Then Target.Offset(0, -1).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
End Sub

 

[akiz009]

............... Thank you Mr. Andrew

If you have time, take a look at my next adventure, Sir...

I tested the given code .... I need to make two changes in this given Vba script.

---->>
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


Sub CopyData_DAILY_REPORT()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Daily Report"

' Fill in the start row.
StartRow = 12

' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

' Find the last row with data on the summary
' and source worksheets.
Last = LastRow(DestSh)
shLast = LastRow(sh)

' If source worksheet is not empty and if the last
' row >= StartRow, copy the range.
If shLast > 0 And shLast >= StartRow Then
'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"summary worksheet to place the data."
GoTo ExitTheSub
End If

' This statement copies values and formats.
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

' AutoFit the column width in the summary sheet.
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
___________________________________

This code is used for copying data from all available Sheets in the work book to a new Sheet.
I Tried to modify this code in this way....


>> I want to Exclude sheet named "Main Sheet" from being copied / executed. (All other available Sheet should be copied)
>> It should copy only "column A" to "Column F". StartRow = 12.... (Current Code copies all the columns, which is not expected. I want to fix the Range)

Thank You ..... .....Thank you again