Everything Excel

If you have ever wanted to display a message box in excel to show information or just to inform the user that a macro is complete it is very simple: Msgbox(“Your Message Text Here”) That’s it a message box will then pop up with your message.

The following Macro will allow you to save a specified sheet as a PDF Document Sub Export_PDF() 'Select sheets to export ThisWorkbook.Sheets("Sheet1").Select 'Create the filename to use ‘Change the text “ C:\temp\ Saved ” to the new location you wish to save the spreadsheet ‘Note that “ Saved ” is the name of the PDF filename = " C:\temp\ Saved ” 'Save the selected sheets as a PDF using the above filename ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _    filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, _     IgnorePrintAreas:=False, OpenAfterPublish:=False End Sub

This macro will save a specified sheet as a new workbook: Sub Save_Sheet() ‘Change the text “ Sheet1 ” to the name of the sheet you wish to save    ThisWorkbook.Sheets(" Sheet1 ").Copy    With ActiveSheet.UsedRange        .Copy        .PasteSpecial xlValues    End With ‘The “DisplayAlerts = False” will stop Excel from displaying the “Save Dialog”    Application.DisplayAlerts = False    Application.CutCopyMode = False ‘Change the text “ C:\temp\ Saved ” to the new location you wish to save the spreadsheet ‘Note that “ Saved ” is the name of the spreadsheet    ActiveWorkbook.SaveAs " C:\temp\ Saved " ‘This will close the workbook after saving ‘You can remove this line if you want the new workbook to stay open    ActiveWorkbook.Close End Sub

Using the SUMIF Formula we can add up all the results in a range when another range matches the a set criteria In the example below we find any cell which = “Yes” in the range D2:D6 then add the results from B2:B6 =SUMIF(D2:D6,"Yes",B2:B6) Explained =SUMIF( D2:D6 ,"Yes", B2:B6 ) This is the function we are calling to use This is the range we want to match our criteria too This is the criteria we want to match This is the range we want to add together if the above matches Also please see my instruction video for a more detailed step by step guide:

Using a Vlookup Formula we can use a cell to search for a value in a table In the example below we use the search value in: B8 and the table range (A1:C6) And display the information in the 2 nd column in cell B9 =VLOOKUP(B8,A1:C6,2,FALSE) Explained: =VLOOKUP( B8, A1:C6, 2, FALSE ) This is the Function we are calling to use. This is the cell which hold the value we want to search for This is the range of the table we want to search in This is the column number of the range the result is stored in This is set to false which means “Exact Match” Also please see my instruction video for a more detailed step by step guide:

Set cell to current date. =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) Format the Text for the current date =TEXT(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))," DD MMMM YYYY ") Result Format As 15 August 2017 DD MMMM YYYY 15 DD Tue DDD Tuesday DDDD 08 MM Aug MMM August MMMM 17 YY 2017 YYYY Add text to the formatted date text ="Today is: " & TEXT(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),"DDDD DD MMMM YYYY")