Copy Print Settings From One Sheet To Another (Excel 2007-2010)

1) Select the sheet tab containing the print settings you want to duplicate;

2) Hold down the CTRL key and select the sheet tab(s) you want to copy those print settings to; (You are grouping the worksheets by doing this)

3) On the Page Layout tab, click the small arrow (dialog launcher) on the bottom right of the Page Setup group and click OK.

Ungroup the sheets by clicking on one Worksheet tab name.

Excel Training Classes in NYC and Live Online Excel Courses.

If statement contains text (Excel 2010 Formula)

=IF(SEARCH(“Text”,A2)),”Yes”, “No”)

This checks if cell A2 has Text anywhere inside of it.

Extracting the 2nd (Second), 3rd (Third) or 4th (Fourth) Word from a Cell (Excel 2010)

Extracting the 2nd (Second) Word from a Cell
Text is in A6

=MID(MID(MID(SUBSTITUTE(A6,” “,”^”,1),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,1)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,1),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,1)),256))-2)

Extracting the 3rd (Third) word from a Cell

=MID(MID(MID(SUBSTITUTE(A6,” “,”^”,2),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,2)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,2),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,2)),256))-2)

If you want to extract the 4th word, change all the 2’s to 3’s

If you wanted to extract the 50th word, change all the 2’s to 49’s

Note: This does not work for the last word.
Use this for the last word

=RIGHT(A6,LEN(A6)-FIND(“*”,SUBSTITUTE(A6,” “,”*”,LEN(A6)-LEN(SUBSTITUTE(A6,” “,””)))))

Excel Training Course Classes in NYC on live online classes

Excel SUMIF/COUNTIF contains text

Use the SUMIF function as shown in the following formulas:
1. =SUMIF(A2:A7,”Excel”,A:A)- Only Sums Excel
2. =SUMIF(A2:A7,”Excel*”,A:A)- Starts with Excel
3. =SUMIF(A2:A7,”*Excel”,A:A)- Ends with Excel
4. =SUMIF(A2:A7,”*Excel*”,A:A)- Contains Excel
5. =SUMIF(A2:A7,”???”,B2:B7)- Is Three Characters.

Excel Training Class in NYC or Online that teaches this information.

VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file

VBA CODE IS FOUND HERE

Google found many keywords in our site from blogs and homework files that were hurting our SEO and making our site less relevant to our specific Keywords such as Excel Training in NYC or VBA Training. By checking where each keyword was getting picked up, we could block that page from being index or delete old files.

This macro separates all the links (up to 7) per line and allows you to click them to check if you should block that page by a robots.txt file.

VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file (CODE)

Dim lastrow As Integer
Dim c As Range
‘find last row
Range(“a65000”).End(xlUp).Select
lastrow = ActiveCell.Row

‘insert last row
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“\$A\$1:\$D\$” & lastrow), , xlYes).Name = _
“Table1”
Range(“Table1[#All]”).Select
ActiveSheet.ListObjects(“Table1”).TableStyle = “TableStyleLight9”

Range(“Table1[Top URLS]”).Select
Selection.Replace What:=”[“, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=”]”, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “WithSpace”
Range(“E2”).Select
ActiveCell.FormulaR1C1 = “=SUBSTITUTE([@[Top URLs]],””:http://””,”” http://””)”

‘select table column
Range(“Table1[WithSpace]”).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns(“E:E”).Select
Application.CutCopyMode = False
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)) _
, TrailingMinusNumbers:=True

Range(“Table1[Top URLS]”).Delete
Range(“Table1[#All]”).ColumnWidth = 25
Range(“Table1[WithSpace]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column1]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column2]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column3]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column4]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column5]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column6]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column7]”).Select
For Each xCell In Selection
Next xCell
Range(“Table1[Column8]”).Select
For Each xCell In Selection
Next xCell

End Sub

Excel 2010 Macro to Do something to a Dynamic Range

‘VBA Excel Training Classes and Courses in New York City, NYC

Sub OnlyDoIfValue()
Dim lastrow As Long, mycell As Object

‘Find the last row in Excel

Range(“a65000”).End(xlUp).Select
lastrow = ActiveCell.Row

‘Define the Dynamic Range
Set myRange = Range(“b1:b” & lastrow)

For Each mycell In myRange
‘Puts in something to the right of the range

mycell.Offset(0, 1) = “Something”
Next
End Sub

Excel 2010 VBA to assign a shortcut key

Application.OnKey “^j”, “subroutineToRun”

This runs the subroutine named subroutineToRun when CTRL+j is pressed.
The macros must be run once before the shortcut will work.

Here’s a full list of other keys

VBA Assign Shortcut key reference

To deassign/release the shortcut leave the Procedure empty
Application.OnKey “%b”, “”

Disable Save (Ctrl S)
Application.OnKey “^s”, “”

Macro to close the current worbook without saving and reopen the same workbook

Sub ClosewithoutsaveAndReopen()

‘Defines Variables

Dim nameOfCurrentWorkbook As Variant

Dim nameOfCurrentWorkbookPath As Variant

‘Finds the name of the current workbook

nameOfCurrentWorkbook = ActiveWorkbook.Name

‘finds the current path of hte workbook

nameOfCurrentWorkbookPath = ActiveWorkbook.Path

ActiveWorkbook.Close False

‘Reopens the same worbook you just closed

Workbooks.Open (nameOfCurrentWorkbookPath & “\” & nameOfCurrentWorkbook)

End Sub