Hyperlink

[Roman12]

Am I able to create a hyperlink which can be put into the toolbar? E.g. creating a macro and the put a macro button into the toolbar (tools/customize/macros).

 

[Stormseed]

Yes, you can create a button, assign a particular macro to the newly created button and you can add the button to an existing toolbar or a custom toolbar in your Excel Application.

 

[Roman12]

The hyperlink (button in the toolbar) should always go back to the frontpage in the excel. I just found out, that one is able to create a hyperlink, but unfortunately only to another document and not within the existing file. Do you know how to bypass that problem?

 

[Stormseed]

You are talking about two different things.

1) Is it that you want to create a hyperlink (another document or the same existing file -does not really matter) ?
2) OR is it that you want to create a button to which a macro is assigned and is added on the toolbar of Excel application ?

P.S: please note that once the button is created (irrespective of macros assigned to the button) and added in a toolbar of Excel, it would be available and would be visible to all the open Workbooks in Excel.

 

[Roman12]

I just thought that it could be possible to create a macro with the same command. But I do not want the button to exist in the other excel files as well. Thus I have to find another solution. Thank you very much

P.S. The following macro always deletes the sheets when calculating and updating the new data every week. Would it be possible to change the macro that it would not delete those sheets with the new data (like the chart sheets?? This would enable me to make a normal hyperlink on the different sheets.

Sub SortSheets()
Dim c As Range
Dim lr As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Total data" And ws.Name <> "KPITopLevel" And ws.Name <> "pivot" And ws.Name <> "Frontpage" And ws.Name <> "Chart Total" And ws.Name <> "Chart IBM Global" _
And ws.Name <> "Chart Networking Services" And ws.Name <> "Chart Security and Risk Mgmt" _
And ws.Name <> "Chart Server Systems Operations" And ws.Name <> "Chart Service Management" _
And ws.Name <> "Chart TechIntMgt" And ws.Name <> "Chart Asset Mgmt" _
And ws.Name <> "Chart End User Support" And ws.Name <> "Chart Geo Service Delivery" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
With Sheets("Total data")
lr = .Range("a" & Rows.Count).End(xlUp).Row
.Range("a2:a" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Z1"), Unique:=True
For Each c In .Range("Z2", .Range("Z2").End(xlDown))
.Copy After:=Sheets(Sheets.Count)
With ActiveSheet.Range("a2:a" & lr)
.AutoFilter Field:=1, Criteria1:="<>" & c.Value
.Offset(1).EntireRow.Delete
.AutoFilter
.Offset(, 24).EntireColumn.Delete
.Parent.Name = c.Value
End With
Next c
.Columns("Z").Delete
End With
Application.ScreenUpdating = True
End Sub

 

[Stormseed]

The macro that you have posted instructs Excel to delete the sheets if the name of the worksheet is not equal to the names which you have specified in the macro, like for e.g., Total data,KPITopLevel,pivot,Frontpage,Chart Total,Chart IBM Global,etc.

The code line ws.Delete deletes these sheets. If you make someone edit this line of the macro without explaining him / her of what you need to accomplish in your application, it may happen so that the macro wont work the way you want it to work. It is always advisable to ask the person who created this macro for you in order to make changes according to your requirement.

Also, if someone on the board, maybe an expert on VBA macros, may come up with a solution to your problem. I can read what the macro is doing in your application but I would inform you that I am an average runner and a learner when VBA comes into picture. Hence I would refrain giving you advice on editing your line of code. My apologies for being dumb when it comes to VBA.

 

[Roman12]

No problem. Thank you for your help