excelprovegue

[excelprovegue]

VBA Macro to Remove Dead Names from Workbook

As an Excel enthusiast, I often work with complex workbooks that include numerous named ranges. These names help streamline formulae and improve workbook readability. However, during a recent project, I encountered an issue: a large number of these named ranges were broken, referring to invalid references (#REF!). This not only caused errors but also made it challenging to navigate and manage the…

[excelprovegue]

Remove Unwanted styles from worksheet...

Remove Unwanted styles from worksheet…

Sub Remove_unwanted_Styles()

Dim style               As style

Dim l_counter           As Long

Dim l_total_number      As Long

  On Error Resume Next

  l_total_number = ActiveWorkbook.Styles.Count

Application.ScreenUpdating = False

  For l_counter = l_total_number To 1 Step -1

  Set style = ActiveWorkbook.Styles(l_counter)

  If (l_counter Mod 500 = 0) Then

DoEvents

Application.StatusBar =…

[excelprovegue]

How to Crack VBA password...

How to Crack VBA password…

Private Const PAGE_EXECUTE_READWRITE = &H40 Private Declare PtrSafe Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As LongPtr, Source As LongPtr, ByVal Length As LongPtr) Private Declare PtrSafe Function VirtualProtect Lib "kernel32" (lpAddress As LongPtr, _ ByVal dwSize As LongPtr, ByVal flNewProtect As LongPtr, lpflOldProtect As LongPtr) As LongPtr Private Declare PtrSafe…

[excelprovegue]

Hyperion Connect code...

Hyperion Connect code…

Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long Sub My_HypConnect() X=HypConnect(Empty, UserName, Password, "My Sample Basic") End Sub

[excelprovegue]

Hyperion Refresh Code...

Hyperion Refresh Code…

Option Explicit Declare PtrSafe Function HypMenuVRefresh Lib “HsAddin” () As Long ‘ This is required for the SmartView data refresh function

Sub HP_Refresh() Dim X As Variant ‘ a variable to store the type of error message, if any, returned from the Hyperion refresh ‘refresh the checks tab:

X = HypMenuVRefresh() Application.Calculate

End Sub

[excelprovegue]

Consolidate Visible tabs data into Master Sheet ...

Consolidate Visible tabs data into Master Sheet …

  Note : Master tab is the tab where you are going to consolidate the data so please as per this code change the tab name to Master Sub DATA_Console() Dim WS_Count As Integer Dim I As Integer ' Set WS_Count equal to the number of worksheets in the active workbook. WS_Count = ActiveWorkbook.Worksheets.Count ' Loop Begin For I = 2 To WS_Count - 2  ' 2 (Sheet starts from)  To WS_Count (Total number…

[excelprovegue]

Update a table of contents To update a table of contents that was created automatically from heading styles, click References > Update Table. You can choose to Update page numbers only or Update entire table if you want to update the page numbers and the text.

[excelprovegue]

Lookup Function to Get Last Matching Value… In this article we are going to learn, how we can use lookup function to get last matching value out of multiple values. Function : =LOOKUP(2,1/($A$2:$A$8=D2),$B$2:$B$8)

[excelprovegue]

How to Open Latest File in a Folder?

How to Open Latest File in a Folder?

Option Explicit Sub Latest_File()     Dim MyPath As String     Dim MyFile As String     Dim LatestFile As String     Dim LatestDate As Date     Dim LMD As Date     MyPath = “C:\Users\Documents\”     If Right(MyPath, 1) <> “\” Then MyPath = MyPath & “\”     MyFile = Dir(MyPath & “*.xls”, vbNormal)     If Len(MyFile) = 0 Then         MsgBox “No files were found…”, vbExclamation        …

[excelprovegue]

Indirect Function

The Microsoft Excel INDIRECT function returns the reference to a cell based on its string representation. The INDIRECT function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Example :

It’s difficult to explain in writing, but we can make a simple practical example for you to understand: a) Write your name in cell A1 b) Type in cell C1: = INDIRECT (“A1”)  the…

[excelprovegue]

Formula is not calculating… When a formula does not calculate, you have to check if automatic calculation is enabled in Excel.

[excelprovegue]

Save a workbook in other file format 2013/2016

Save a workbook in other file format 2013/2016

Open the workbook you want to save.

Click File > Save As.

Under Places, pick the place where you want to save the workbook. For example, pick OneDrive to save it to your Web location or Computer to save it in a local folder like your Documents.

In the Save As dialog box, navigate to the location you want.

In the Save as type list, click the file format you want. Click the arrows to scroll to…

[excelprovegue]

Rotate / Change the orientation of your entire document

Rotate / Change the orientation of your entire document

Click > Page Layout > Orientation.

Click Portrait or Landscape.

Note:  When you change the orientation, the cover pages in the Cover Page gallery change to the orientation you’ve chosen.

Use different orientations in the same document

There may be times when you want certain pages or sections of your document to have a different orientation from the rest of it. Note that Word puts selected…

[excelprovegue]

Remove the page number from the first page

Remove the page number from the first page

On the Insert tab, click Header > Edit Header (or Footer > Edit Footer).

On the Header & Footer Tools Design tab, in the Options group, select Different First Page.

Note: Different first page can be applied to the first page of any section in your document, not just the very first page.

In the header or footer area, you should see a label on the first page that says First Page Header.…

[excelprovegue]

Change the default font in Power Point

Change the default font in Power Point

Click View > Slide Master.

On the Slide Master tab, click the Fonts drop-down menu. Select the font you want to use for all the slides in the presentation.

Note: Any changes you make in this Slide Master view affect all the other slide templates.

Click Close Master View. The text throughout your presentation will automatically update to the new font.

Create a template to save your default…

[excelprovegue]

Set default font...

Set default font…

Start with a blank document, or if your document already contains text that is formatted with the properties that you want to use, select that text.

On the Home tab, click the Font Dialog Box Launcher, and then click the Font tab.

Select the options that you want to apply to the default font, such as font style and font size. If you selected text in step 1, the properties of the selected text…

[excelprovegue]

Insert Word ART Click where you want to insert decorative text in a document. On the Insert tab, in the Text group, click WordArt. Click any WordArt style, and start to type.