Skip to content

Instantly share code, notes, and snippets.

View ateneva's full-sized avatar

ateneva

5 followers · 23 following
  • Amsterdam
View GitHub Profile
@ateneva
ateneva / ForEachPT_ResetPTDefaultSettings.bas
Last active March 26, 2018 17:16
Reset PivotTable Default Settings
Sub ResetPTDefaultSettings()
Dim Wks As Worksheet
Dim PT As PivotTable
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, 17/09/2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
@ateneva
ateneva / ForEachWbk_RefreshAllConnections.bas
Last active April 16, 2017 16:16
Refresh all external connections for a list of workbooks
Sub RefreshMonthly()
Dim Cell As Range
Dim Source As String
Dim Target As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Application.ScreenUpdating = False
Application.DisplayAlerts = False
@ateneva
ateneva / ForEachWbk_TweakAllFiles.bas
Last active February 18, 2018 21:23
Quickly update the worksheet structure in multiple workbooks
Sub TweakAllFiles()
Dim Cell As Range
Dim path As String
Dim file As String
Dim fullfilepath As String
'------------------------------------------------------------
'written by Angelina Teneva, 2017
'-----------------------------------------------------------
Application.DisplayAlerts = False
For Each Cell In ThisWorkbook.Worksheets("UCAS").Range("A2:A103")
@ateneva
ateneva / ForEachTableInWbk_FilterAllTablesInWbk.bas
Last active February 17, 2018 21:22
Option Explicit
Sub FilterAllTablesInActiveSheet()
Dim Wks As Worksheet
Dim T As ListObject
Dim i As Integer
Dim bU As String
bU = ActiveSheet.Range("C6").Value
@ateneva
ateneva / ForEachWks_AddLogo.bas
Last active January 7, 2018 17:37
Add logo to the top left most corner of each worksheet in your workbook
Sub logo()
Dim Wks As Worksheet
Dim Sh As Shape
Dim x As Integer
Dim y As Integer
Dim Cell As Range
'******************************************
@ateneva
ateneva / ForEachWks_BeautifyWks.bas
Last active April 22, 2017 13:46
Beuatify your spreadsheet by removing the gridlines on every worksheet and resizing it to 80%
Sub RemoveGridlines()
Dim Wks As Worksheet
'--------------------------------
'written by Angelina Teneva, 2014
'--------------------------------
For Each Wks In ActiveWorkbook.Worksheets
If Wks.Visible = True Then Wks.Activate
@ateneva
ateneva / UpdatePaths.bas
Created April 22, 2017 10:58
Quickly update desktop paths depending on who is using the workbook at the moment
Private Sub Workbook_Open()
Dim Cell As Range
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Application.Calculation = xlCalculationAutomatic
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'update paths
Worksheets("MACROS").Activate
If Application.UserName = "Angelina Teneva" Then
@ateneva
ateneva / ForEachCell_AuthUser.bas
Last active July 21, 2018 23:47
Only hide worksheets if the user is authorises to view them
Sub AuthUser()
Dim Cell As Range
Dim person As String
Dim authperson As String
person = Application.UserName
'----------------------------------------
'written by Angelina Teneva, 2013
'-----------------------------------------
@ateneva
ateneva / ForEachWks_ToggleWksProtection.bas
Last active January 20, 2018 12:11
Toggle worksheet protection on/off
Sub KeepData()
Dim Wks As Worksheet
'~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva
'toggling sheet and workbook protection on/off with a password
If ActiveWorkbook.ProtectStructure = True Then
ActiveWorkbook.Unprotect ("annie")
@ateneva
ateneva / ForEachPT_RefilterAllPivotTables.bas
Last active April 22, 2018 08:45
Refilter pivot tables that have different pivot caches (come from different sources)
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim Ans As String
Dim Ans2 As Integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva 2013
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~