'Files Sheet Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Me ' G address last address used 'click out version ... If .[G_input].Address = .[Sel_address].Value Then _ DoGsearch 'MsgBox "got G input", , Now .[sel_content].Value = Target.Value .[Sel_address].Value = Target.Address 'dirs work 12 3 20 fixKTblanks fixKT1 'this file .[ondate] = Date .[attime] = Now End With End Sub 'k3rebuild Sub three_month_filler() 'replaces 3 moth calenar w VBA '? now 28/07/2015 21:01:04 ' Macro1 Macro Application.DisplayAlerts = False Dim kkSheet As Worksheet Dim kkRange As Range Dim dc As Range Set kkSheet = ThisWorkbook.Worksheets("threemonth") kkSheet.Activate k_clearshapes With kkSheet Cells.Clear Set kkRange = kkSheet.Range("D16:J16") With kkRange 'day of week names .Cells(1, 1).Value = "Sun" .Cells(1, 1).AutoFill Destination:=kkRange, Type:=xlFillDefault .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom End With Set kkRange = kkRange.Offset(1, 0) With kkRange 'current week .Cells(1, 1).Value = Date - Weekday(Date) + 1 '"Sun start always" .Cells(1, 1).AutoFill Destination:=kkRange, Type:=xlFillDefault End With For toff = 1 To 10 'next 10 weeks Set kkRange = kkRange.Offset(1, 0) For Each dc In kkRange dc.Value = dc.Offset(-1, 0).Value + 7 'next 10week of course Next dc Next toff Set kkRange = kkRange.Offset(-12, 0) 're set range For Each dc In kkRange dc.Value = dc.Offset(2, 0).Value - 7 'last week Next dc For toff = 1 To 10 '10 more bacj Set kkRange = kkRange.Offset(-1, 0) For Each dc In kkRange dc.Value = dc.Offset(1, 0).Value - 7 'last week of course Next dc Next toff Set kkRange = .[d5].CurrentRegion For Each dc In kkRange If IsDate(dc.Value) Then dc.NumberFormat = "d" If (Day(dc.Value)) = 15 Then 'found row for mon label With Cells(dc.Row, 11) .Value = dc.Value .NumberFormat = "mmmm yy" .Style = "Heading 2" End With End If With dc.Interior .Pattern = xlSolid .PatternTintAndShade = 0 .PatternColorIndex = xlAutomatic Select Case fixMends(Month(dc.Value)) Case Month(Date) .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.399975585192419 Case Month(Date) - 1 .ThemeColor = xlThemeColorAccent4 .TintAndShade = 0.799981688894314 Case Month(Date) + 1 .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End Select End With End If If dc.Value = Date Then dc.Font.Color = -16776961 Next dc End With tidy3M 'new month labelson means(avs) col k add_DAYoW ActiveSheet adCmonlabel ActiveSheet adCshortDlabel ActiveSheet adtimelaunchlabel ActiveSheet [a1].Select ' was [m1].Value = "DO JOB HUNT _ last day - 31 Aug : " & #8/31/2018# - Date & " Days to go" [m1].Value = "Get Busy!! : " & #2/24/2021# - Date & " Days to GLXW Birthday" Application.DisplayAlerts = True End Sub Function fixMends(mnum As Integer) 'reset the month to help main routine(?) 'only works in Dec & Jan fixMends = mnum If Month(Date) = 12 And mnum = 1 Then fixMends = 13 If Month(Date) = 1 And mnum = 12 Then fixMends = 0 End Function Sub KKformsrepair() 'replaces formula useful 'lame style '? 30/07/2015 13:37:11 Dim kkSheet As Worksheet 'Dim kkRange As Range 'Dim dc As Range Set kkSheet = ThisWorkbook.Worksheets("kkontrol") kkSheet.Range(ActiveCell.Value).Value = "::" & ActiveCell.Offset(0, 1).Value ActiveCell.Offset(1, 0).Select Exit Sub '' Cells.Clear '' Set kkRange = kkSheet.Range("D16:J16") '' '' With kkRange 'day of week names '' .Cells(1, 1).Value = "Sun" '' .Cells(1, 1).AutoFill Destination:=kkRange, Type:=xlFillDefault '' '' End With '' '' Set kkRange = kkRange.Offset(1, 0) '' With kkRange 'current week '' .Cells(1, 1).Value = Date - Weekday(Date) + 1 '"Sun satrt always" '' .Cells(1, 1).AutoFill Destination:=kkRange, Type:=xlFillDefault '' End With '' '' For toff = 1 To 10 'next 10 weeks '' Set kkRange = kkRange.Offset(1, 0) '' For Each dc In kkRange '' dc.Value = dc.Offset(-1, 0).Value + 7 'next 10week of course '' Next dc '' '' Next toff '' Set kkRange = kkRange.Offset(-12, 0) 're set range '' '' For Each dc In kkRange '' dc.Value = dc.Offset(2, 0).Value - 7 'last week '' Next dc '' '' For toff = 1 To 10 '10 more bacj '' Set kkRange = kkRange.Offset(-1, 0) '' For Each dc In kkRange '' dc.Value = dc.Offset(1, 0).Value - 7 'next week of course '' '' Next dc '' Next toff '' '' Set kkRange = .[d5].CurrentRegion '' For Each dc In kkRange '' If IsDate(dc.Value) Then '' With dc.Interior '' .Pattern = xlSolid '' .PatternTintAndShade = 0 '' .PatternColorIndex = xlAutomatic '' Select Case Month(dc.Value) '' Case Month(Date) - 1 '' '' .ThemeColor = xlThemeColorAccenkk '' .TintAndShade = 0.399975585192419 '' '' '' Case Month(Date) '' .ThemeColor = xlThemeColorAccent1 '' .TintAndShade = 0.599993896298105 '' '' Case Month(Date) + 1 '' .ThemeColor = xlThemeColorLight2 '' .TintAndShade = 0.799981688894314 '' '' End Select '' End With '' End If '' Next dc End Sub Sub tidy3M() ' ' Macro2 Macro ' ' Columns("A:C").Select Selection.ColumnWidth = 0.75 Range("D1").Select ActiveWindow.DisplayGridlines = False With [D1:F1] .Value = "Three month Calendar" .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Merge .Style = "Heading 1" End With Range("j1").Value = Format(Date, "dddd dd mmmm yyyy") With [D3:g3] .Value = "Current Month is " & Format(Date, " mmmm yyyy") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Merge .Style = "Heading 2" End With End Sub Sub collog() 'keeping better tabs on myself ... 'MsgBox ThisWorkbook.FullName Dim audit_sheet As Worksheet, colRange As Range Set audit_sheet = ThisWorkbook.Sheets("Audit_trail") NextRow = realRows(audit_sheet) With audit_sheet Set colRange = Intersect(.Rows(NextRow), .[a:m]) With colRange.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 15773696 .TintAndShade = 0 .PatternTintAndShade = 0 End With End With End Sub Sub Gsearch1(term) 'not mine 14 7 15 WIP Dim url As String, trow As Integer, STsheet As Worksheet Set STsheet = ThisWorkbook.Worksheets("searchterms") url = "https://www.google.co.uk/?gfe_rd=cr&ei=ll_MVcbdDcaB7Qbr_oSwBg&gws_rd=ssl#q=zzzyyy+" url = "https://www.google.co.uk/#q=droitwich" url = "https://www.google.co.uk/#q=" & term 'ActiveCell.Value trow = Application.WorksheetFunction.CountA(STsheet.[A:A]) + 1 STsheet.Cells(trow, 1).Value = Now STsheet.Cells(trow, 2).Value = term ThisWorkbook.FollowHyperlink (url) End Sub Sub PK_button() ' ' PK_button Macro ' panel_K.Show ' End Sub Sub pk__Shout() ' ' Macro3 Macro fun with chained subs due to no userform ONGOING >>> 'saving shouts .. Dim trow As Integer, STsheet As Worksheet Set STsheet = ThisWorkbook.Worksheets("searchterms") trow = Application.WorksheetFunction.CountA(STsheet.[q:q]) + 1 STsheet.Cells(trow, 17).Value = Now STsheet.Cells(trow, 18).Value = panel_K.TextBox1.Value Application.ScreenUpdating = False Workbooks.Add Application.DisplayFullScreen = True Application.DisplayFormulaBar = False ActiveWindow.DisplayHeadings = False ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 80 [a42].Activate With Range("A3:o20") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .Font.Size = 48 End With Range("A3:o20").Merge Range("A3:o20").Value = panel_K.TextBox1.Value ActiveWindow.SmallScroll Down:=3 '/12 '' ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 41.25, 67.5, 573, _ '' 357.75).Select '' Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle '' Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = panel_K.TextBox1.Value '' With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 10). _ '' ParagraphFormat '' .FirstLineIndent = 0 '' .Alignment = msoAlignCenter '' End With '' With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 10).Font '' .NameComplexScript = "+mn-cs" '' .NameFarEast = "+mn-ea" '' .Fill.Visible = msoTrue '' .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1 '' .Fill.ForeColor.TintAndShade = 0 '' .Fill.ForeColor.Brightness = 0 '' .Fill.Transparency = 0 '' .Fill.Solid '' .Size = 88 '' .Name = "+mn-lt" ' End With Application.ScreenUpdating = True Application.OnTime Now + TimeValue("00:00:05"), "pk_tidy" End Sub Sub pk_tidy() Application.ScreenUpdating = False 'For i = 1 To 1000000: d = Sqr(i): q = d * d: Next ActiveWindow.Close False ActiveWindow.DisplayGridlines = True Application.DisplayFormulaBar = True Application.DisplayFormulaBar = True Application.ScreenUpdating = True Application.DisplayFullScreen = False panel_K.Show End Sub Sub add_DAYoW(m3sheet As Worksheet) ' Dim dow_shape As Shape Set dow_shape = m3sheet.Shapes.AddTextEffect(msoTextEffect36, "Your text here", "+mn-lt", _ 54, msoFalse, msoFalse, 306.0575590551, 197.4604724409) 'Range("F4:G6").Select dow_shape.IncrementLeft -0.75 dow_shape.IncrementTop -182.25 dow_shape.TextFrame2.TextRange.Characters.Text = UCase(Format(Date, "dddd")) With dow_shape.TextFrame2.TextRange.Characters.Font .Fill.Visible = msoTrue .Fill.ForeColor.RGB = RGB(83, 87, 92) .Fill.BackColor.RGB = RGB(197, 199, 202) .Fill.TwoColorGradient msoGradientHorizontal, 1 .Size = 54 .line.Visible = msoFalse .Name = "+mn-lt" .Spacing = 0 End With End Sub Sub k_clearshapes() Dim s As Shape For Each s In ActiveSheet.Shapes s.Delete Next End Sub Sub adCshortDlabel(m3sheet As Worksheet) Dim m3_shape As Shape Set m3_shape = m3sheet.Shapes.AddTextEffect(msoTextEffect32, "Your text here", "+mn-lt", _ 54, msoFalse, msoFalse, 306.0575590551, 242.4604724409) With m3_shape .TextFrame2.TextRange.Font.Size = 28 .IncrementLeft 245 .IncrementTop -165 .TextFrame2.TextRange.Characters.Text = Format(Date, "dd mmm yy") End With End Sub Sub adtimelaunchlabel(m3sheet As Worksheet) Dim m3_shape As Shape Set m3_shape = m3sheet.Shapes.AddTextEffect(msoTextEffect32, "Your text here", "+mn-lt", _ 54, msoFalse, msoFalse, 306.0575590551, 242.4604724409) With m3_shape .TextFrame2.TextRange.Font.Size = 28 .IncrementLeft 245 .IncrementTop 80 .TextFrame2.TextRange.Characters.Text = Format(Now, "hh:mm:ss.00") End With End Sub Sub adCmonlabel(m3sheet As Worksheet) Dim m3_shape As Shape Set m3_shape = m3sheet.Shapes.AddTextEffect(msoTextEffect32, "Your text here", "+mn-lt", _ 54, msoFalse, msoFalse, 306.0575590551, 242.4604724409) With m3_shape .TextFrame2.TextRange.Font.Size = 28 .IncrementLeft 245 .IncrementTop -55 .TextFrame2.TextRange.Characters.Text = Format(Date, "mmmm yyyy") End With ' ' With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 12). _ ' ParagraphFormat ' .FirstLineIndent = 0 ' .Alignment = msoAlignCenter ' End With ''' With m3_shape.ShapeRange(1).TextFrame2.TextRange.Characters(1, 12).Font ''' .Bold = msoFalse ''' .Caps = msoNoCaps ''' .NameComplexScript = "+mn-cs" ''' .NameFarEast = "+mn-ea" ''' .Shadow.Type = msoShadow25 ''' .Shadow.Visible = msoTrue ''' .Shadow.Style = msoShadowStyleOuterShadow ''' .Shadow.Blur = 3 ''' .Shadow.OffsetX = 1.2246467991E-16 ''' .Shadow.OffsetY = 2 ''' .Shadow.RotateWithShape = msoFalse ''' .Shadow.ForeColor.RGB = RGB(110, 116, 122) ''' .Shadow.Transparency = 0.5699999928 ''' .Shadow.Size = 100 ''' .Fill.Visible = msoTrue ''' .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1 ''' .Fill.ForeColor.TintAndShade = 0 ''' .Fill.ForeColor.Brightness = 0 ''' .Fill.Transparency = 0 ''' .Fill.Solid ''' .Size = 28 ''' .line.Visible = msoFalse ''' .Name = "+mn-lt" ''' .Spacing = 0 ''' End With ''' Range("L17").Select End Sub Sub junkFib() ' 6 1 15 'v lame fibbonacci SDeleter Sheets.Add [b2] = 0 [b3] = 1 [b4].Select For i = 1 To 40 With ActiveCell .Value = .Offset(-1, 0).Value + .Offset(-2, 0).Value .Offset(1, 0).Select End With Next End Sub 'uty iss Sub LABEL_CODE1() Rem trying to improve commetns and dating of code .. Debug.Print q = Chr(39) & String(80, "=") dp q dp q dp Format(Now(), "'dddd dd mmmm yyyy") dp Format(Now(), "'at hh:mm:ss") dp Chr(39) & String(52, " ") & "Code by Chris N Wright MSc." dp q dp q Debug.Print 'do some Dims and set=s in the same way 'dim xlApp as application For Each C In ThisWorkbook.Sheets("CodeBuilder3").[M78:M84] dp C.Value Next C For Each C In ThisWorkbook.Sheets("CodeBuilder3").[l97:l122] dp C.Value Next C End Sub Function dp(print_Me) dp = 0 Debug.Print print_Me End Function Sub LABEL_CODE2() Rem trying to improve commetns and dating of code .. subName_str = InputBox("Input subroutine/name", "labelcode2", "Temp1auto") q = "sub " & subName_str & " " & Chr(39) & String(10, "-") dp q Debug.Print q = Chr(39) & String(80, "=") dp q dp q dp Format(Now(), "'dddd dd mmmm yyyy") dp Format(Now(), "'at hh:mm:ss") dp Chr(39) & String(52, " ") & "Code by Chris N Wright MSc." dp q dp q Debug.Print 'do some Dims and set=s in the same way 'dim xlApp as application For Each C In ThisWorkbook.Sheets("CodeBuilder3").[M78:M84] dp C.Value Next C For Each C In ThisWorkbook.Sheets("CodeBuilder3").[l97:l122] dp C.Value Next C q = "end sub " & Chr(39) & String(10, "-") & subName_str & " " dp q End Sub Sub shellcheck() '18-10-12 'tring to expoit SHELL to get around ATOS lockdowns 'Shell ("explorer") ' Specifying 1 as the second argument opens the application in ' normal size and gives it the focus. Dim RetVal 'RetVal = Shell("CALC.EXE", 1) ' was RetVal = Shell("C:\WINDOWS\CALC.EXE", 1) '''walkenbach version sstill works '''ChDir WindowsFolder ''''some way to use this >>Application.ActivateMicrosoftApp xlMicrosoftMail 'xlMicrosoftAccess umacsheet = "macrodata" '''Shell "explorer ", vbNormalFocus 'working Shell "explorer " & ThisWorkbook.Sheets(umacsheet).[Af].Value, vbNormalFocus ' Shell "cmd 'c:\' " & ThisWorkbook.Sheets(umacsheet).[diraf].Value, vbNormalFocus ''''' End Sub Sub StartCalculator() 'walkenbach version Dim AppFile As String AppFile = "Calc.exe" On Error Resume Next AppActivate "Calculator" If Err <> 0 Then Err = 0 CalcTaskID = Shell(AppFile, 1) If Err <> 0 Then MsgBox "Can't start Calculator" End If End Sub Sub doTimesUpdate() Dim times As Variant, tbfcell As Range, timesBook As Workbook, UDfoundCell As Range umacsheet = "macrodata" Debug.Print Now() Set tbfcell = ThisWorkbook.Sheets(umacsheet).[b17:b1000].Find("times2013") ' tbfcell.Select Set timesBook = Workbooks.Open(tbfcell.Value, , , , "apex") times = getUpdates With timesBook.Sheets("times ISS") For i = LBound(times, 1) To UBound(times, 1) 'first col is date ' col 2 must be filled in If Len(times(i, 2)) > 0 Then 'assume some dates found Debug.Print times(i, 1); Set UDfoundCell = findDate(CDate(times(i, 1)), .[b3:b1000]) 'fid right datecell For j = LBound(times, 2) + 1 To UBound(times, 2) Debug.Print times(i, j); UDfoundCell.Offset(0, j - 1).Value = times(i, j) Next j Debug.Print End If ''' Next i 'next date and times '' '' End With timesBook.Close True End Sub Function getUpdates() As Variant umacsheet = "macrodata" getUpdates = ThisWorkbook.Sheets(umacsheet).[weeks_update_times].Value End Function Sub getSIMPcal() '' Macro2 Macro '' Sheets("dates central").Select Application.GoTo Reference:="cal_2010" ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.Zoom = 85 End Sub Function lotteryISS() As String Dim lcol, lchk, lotto(6), slotto(6), mpos, isl As Integer Dim Lotstr As String, sLotstr As String, h As String lottr = "Your lottery numbers are :" sLotstr = "=>" h = "-" Randomize restart: lotto(1) = Int((49 * Rnd) + 1) Lotstr = Lotstr & " " & lotto(1) For lcol = 2 To 6 samenum: lotto(lcol) = Int((49 * Rnd) + 1) For lchk = 1 To lcol - 1 If lotto(lcol) = lotto(lchk) Then GoTo samenum Next lchk Lotstr = Lotstr & " " & lotto(lcol) 'why?? not sorted yet! Next lcol ''''Debug.Print Lotstr 'testing only '6 different numbers at this stage For isl = 1 To 6 'pass loop For imin = 1 To 5 'isl ? swaps in a pass ''''Debug.Print isl; h; imin; h; lotto(imin); h; lotto(imin + 1) 'testing only If lotto(imin + 1) < lotto(imin) Then lcol = lotto(imin + 1) lotto(imin + 1) = lotto(imin) lotto(imin) = lcol End If Next imin ' Next isl '6 different numbers at this stage in asc order 'If lotto(1) < 20 Then GoTo restart 'finding nos startin at 20! For isl = 1 To 6 sLotstr = sLotstr & " " & lotto(isl) Next isl Lotstr = "" 'dresponse = MsgBox(Lotstr & sLotstr, , lottr) lotteryISS = sLotstr 'MsgBox ("end of lotto") 'Sheets(shname).Delete 'Application.DisplayAlerts = True End Function Sub cht_exp() ActiveChart.Export "\\issukacpfil001\offlinefiles$\chwright\Desktop\CT.JPG", , True End Sub Sub range_exp() '''[a1:a20].Export "\\issukacpfil001\offlinefiles$\chwright\Desktop\CT.JPG", , True ''[cal_2010].CopyPicture ''Sheets.Add ''Range("a1").Copy End Sub Sub cp_test() Range("b1").Copy Destination:=ActiveCell End Sub Sub cp_test2() 'works Range("A1").Select Selection.Copy Range("B1").Select ActiveSheet.Paste End Sub Sub fill_rangespecial() ''=SUM(Staff1_2012 January_totals) ''+SUM(Staff1_2012 January_adjs) fixrow = 2 fixcol = 1 For Each C In Selection mon = Cells(fixrow, C.Column).Value person = Cells(C.Row, fixcol).Value person = person & "_2012" ' formu = "=sum(" & person & " " & mon & "_totals)" 'formu = formu & " + sum(" & person & " " & mon & "_adjs)" 'C.Value = formu' formu = "=sum(" & person & " " & mon & "_totals)" formu = formu & " + sum(" & person & " " & mon & "_adjs)" C.Value = formu Next C End Sub Sub load_today_calendar() ' 'Thursday,01/11/2012 15:46:22 ' all this because no controls so had to use web browser ... ' CW Dim calTBook As Workbook Dim calTSheet As Worksheet Dim ctfilename As String ulog "updating CT" Application.ScreenUpdating = False Application.DisplayAlerts = False shNfmt = "hh_mm" sstring = "Calendar_today" With ThisWorkbook.Sheets("Macrodata") ctfilename = .[f17:f1000].Find(sstring).Value End With Kill ctfilename Set calTBook = Workbooks.Add Set calTSheet = calTBook.Sheets("Sheet1") calTSheet.Name = Format(Now(), shNfmt) SDeleter CalBuff = ThisWorkbook.Sheets("Macrodata").[cal_2010].Value ThisWorkbook.Sheets("Macrodata").[cal_2010].Copy Destination:=calTSheet.[a1] calTSheet.[a1:k31].Value = CalBuff Columns("A:b").ColumnWidth = 2 Columns("k:k").ColumnWidth = 2 Rows("1:1").RowHeight = 2 ActiveWindow.Zoom = 85 'Columns("C:J").EntireColumn.AutoFit Rows("19:21").Delete Range("A1").Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\station7\Calendar_today.htm", _ FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False '''''''HARD cODED!!!!!!!!!!!!!!!!!!!!!!!!!! ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = False End Sub Sub ChDeleter() 'new power version 'combine w SDeleter? Dim snames As String Dim arr_snames As Variant SEP = "," snames = "" Application.DisplayAlerts = False Set currentSheets = Application.ActiveWorkbook.Sheets 'not worksheets For Each ThisSheet In currentSheets If ThisSheet.Name Like "[cC]hart*" Then snames = snames & SEP & ThisSheet.Name Next ThisSheet If Len(snames) = 0 Then Exit Sub snames = Right(snames, Len(snames) - 1) arr_snames = Split(snames, SEP) Application.ActiveWorkbook.Sheets(arr_snames).Delete Application.DisplayAlerts = True End Sub Sub get_hotmail_ISS_ver() 'D O N T W O R K ! ! ' k:k multi acc hdelay = 2 sstring = "https://login.live.com" sstring = "/login.microsoftonline.com/" With ThisWorkbook.Sheets("Macrodata") Set tbfcell = .[f17:f1000].Find(sstring) End With tbfcell.Select acc = ThisWorkbook.Sheets("macrodata").[hacc k:k].Value pwd = ThisWorkbook.Sheets("macrodata").[hpwd k:k].Value ThisWorkbook.Sheets("macrodata").[Af].Hyperlinks(1).Follow Breather 3 * hdelay SendKeys acc 'Breather hdelay 'Breather hdelay 'SendKeys "w" 'Breather hdelay 'SendKeys "{Down}" 'Breather hdelay 'SendKeys "{Down}" Breather hdelay SendKeys "{ENTER}" Breather hdelay * 2 SendKeys "{TAB}" adpasswd End Sub Sub monthHeadingSPlanner() 'sets headings for mobins mark3 Calednar '' Macro2 Macro Dim dateCell As Range, dactive As Date, allMonthrange As Range, curMonth As Integer, headingMonthrange As Range [G6].Select Set dateCell = ActiveCell dactive = dateCell.Value textmonth = Format(dateCell.Value, " mmmm yyyy") curMonth = Month(dactive) Set allMonthrange = dateCell cind = 8 Do While Len(dateCell.Value) > 0 Set dateCell = dateCell.Offset(0, 1) 'next cell going right dactive = dateCell.Value If curMonth = Month(dactive) Then 'add to region Set allMonthrange = Union(allMonthrange, dateCell) Else ' new month 'dont add yet 'select only allMonthrange.Select Set headingMonthrange = allMonthrange.Offset(-2, 0) bordAllMed headingMonthrange With headingMonthrange .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Interior.ColorIndex = cind .Value = textmonth End With textmonth = Format(dateCell.Value, " mmmm yyyy") dactive = dateCell.Value curMonth = Month(dactive) Set allMonthrange = dateCell cind = cind + 1 End If Loop End Sub Sub PPC1() 'in the lame style 'extr prop codes Dim C As Range For Each C In [d2:d3300].SpecialCells(xlCellTypeBlanks) Application.StatusBar = C.Row C.Value = kill_round_brk(C.Offset(0, -2).Value) With C.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent5 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With Next C End Sub Sub PPC2() 'in the lame style 'fixing extr prop codes 'saving sortcodes where given ... Dim C As Range For Each C In [c2:c3300] Application.StatusBar = C.Row ''''MsgBox IsNumeric(ActiveCell.Value) 'works!! in imm mode If IsNumeric(C.Value) Then C.Offset(0, 1).Value = C.Value With C.Offset(0, -1).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent5 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With C.ClearContents End If Next C End Sub Sub PPC3() 'in the lame style 'extr prop codes Dim C As Range For Each C In [c2:c3300].SpecialCells(xlCellTypeBlanks) Application.StatusBar = C.Row C.Offset(0, 1).Value = kill_round_brk(C.Offset(0, 4).Value) With C.Offset(0, 1).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With Next C End Sub Sub Stripes3() '26 11 12 ' Macro3 Macro' Dim CORNER As Range wid = 14 Windows("2012 2013 Projects Planner - Nov2012.xlsx").Activate Set CORNER = [a3] Do While CORNER With Range(CORNER, CORNER.Offset(0, wid)) With .Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End With Set CORNER = CORNER.Offset(1, 0) With Range(CORNER, CORNER.Offset(0, wid)) With .Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With End With Set CORNER = CORNER.Offset(1, 0) With Range(CORNER, CORNER.Offset(0, wid)) With .Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With End With End Sub Sub goodstripes__ISS() 'SELECTING 'TIDY ME UP 'no selects ''''''''''''''''reset to zero value sub script numcol = 1 ' numcol = ActiveCell.CurrentRegion.Columns.Count colcode = Array(14, 23, 28, 31, 41, 42) 'Bluey Green version ... 'Dim colcode(20) As Integer 'colcode = Array(2, 24, 19, 20, 35, 34, 40, 42, 33, 34, 15, _ 36, 37, 39, 40, 2, 42, 19, 45) 'pastels 'c , olcode = Array(15, 48, 16) 'greyversion ... colcode = Array(2, 19, 35, 34, 15) 'pastels cur = 0 Do While Len(ActiveCell.Value) > 0 'stops on blank With Range(ActiveCell, ActiveCell.Offset(0, numcol)) With .Interior .ColorIndex = colcode(cur) .Pattern = xlSolid End With End With 'With .Borders(xlBottom) ' .LineStyle = xlContinuous ' .Weight = xlThin 'End With With ActiveCell If .Offset(1, 0).Value <> .Value Then cur = cur + 1: If cur > UBound(colcode) Then cur = 0 With Range(ActiveCell, ActiveCell.Offset(0, numcol)).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin End With End If .Offset(1, 0).Select End With Loop End Sub Sub time5minMore() fmin = 1 / (24 * 12) ActiveCell.Value = ActiveCell.Value + fmin End Sub Sub time5minLess() fmin = 1 / (24 * 12) ActiveCell.Value = _ ActiveCell.Value - fmin End Sub Sub addsVARILINK() '27/11/2012 ' Macro1 Macro ' With ActiveCell ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:= _ .Value, TextToDisplay:=.Value End With End Sub Sub FormatAllComments() 'www.contextures.com\xlcomments03.html Dim WS As Worksheet Dim cmt As Comment For Each WS In ActiveWorkbook.Worksheets For Each cmt In WS.Comments With cmt.Shape.TextFrame.Characters.Font .Name = "Times New Roman" .Size = 12 End With Next cmt Next WS End Sub 'workbook, including the sheet name, cell address, and cell name, if any. Sub ShowCommentsAllSheets() 'modified from code 'posted by Dave Peterson 2003-05-16 Application.ScreenUpdating = False Dim commrange As Range Dim mycell As Range Dim WS As Worksheet Dim newwks As Worksheet Dim i As Long Set newwks = Worksheets.Add newwks.Range("A1:E1").Value = _ Array("Sheet", "Address", "Name", "Value", "Comment") For Each WS In ActiveWorkbook.Worksheets On Error Resume Next Set commrange = WS.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 If commrange Is Nothing Then 'do nothing Else i = newwks.Cells(Rows.Count, 1).End(xlUp).Row For Each mycell In commrange With newwks i = i + 1 On Error Resume Next .Cells(i, 1).Value = WS.Name .Cells(i, 2).Value = mycell.Address .Cells(i, 3).Value = mycell.Name.Name .Cells(i, 4).Value = mycell.Value .Cells(i, 5).Value = mycell.Comment.Text End With Next mycell End If Set commrange = Nothing Next WS 'format cells for no wrapping, remove line break newwks.Cells.WrapText = False newwks.Columns("E:E").Replace What:=Chr(10), _ Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False Application.ScreenUpdating = True End Sub Sub handyScraps() Dim SCRAP As String For Each C In [a4:a690] If Len(C.Value) = 0 Then C.Offset(0, 1).Value = SCRAP SCRAP = "" Else SCRAP = SCRAP & " " & C.Value End If Next C End Sub Function squashData(dataRange As Range) As String 'only takes first area of DataRange Dim i As Integer, j As Integer Dim buf As Variant 'holds data Dim sq_temp As String sep1 = "|" '<<<<<<<<<<<<<<<<<<<<<<<<<< sep2 = Chr(10) squashData = "not finished" sq_temp = "" With dataRange 'Get values as an array buf = .Value For i = LBound(buf, 1) To UBound(buf, 1) 'through each record For j = LBound(buf, 2) To UBound(buf, 2) 'through each field sq_temp = sq_temp & buf(i, j) & sep1 ' adding commas Next j sq_temp = Left(sq_temp, Len(sq_temp) - Len(sep1)) & sep2 'remove floating comma adds separator Next i squashData = Left(sq_temp, Len(sq_temp) - Len(sep2)) 'remove floating comma End With End Function Sub StretchData(sqdata As String, StretchDataRange As Range) 'use dropout routine ??????????????????? Dim i As Integer, j As Integer, rLim As Integer Dim sCorner As Range Dim rows_array As Variant 'holds data' IS THIS RIGHT could be! Dim dr(), dr_Proper() 'the process creates a weird array in array so I tidy it into Dr_Proper ' need sensible REdims sep1 = "|": sep2 = Chr(10) Set sCorner = StretchDataRange.Cells(1, 1) 'use split carefully to load up array rows_array = Split(sqdata, sep2) '>>>>>>>>>>>>>now we know the number rows so redim ... rLim = UBound(rows_array, 1) ReDim dr(0 To rLim) For i = LBound(rows_array, 1) To UBound(rows_array, 1) dr(i) = Split(rows_array(i), sep1) Next i cLim = UBound(dr(1), 1) ReDim dr_Proper(0 To rLim, 0 To cLim) For i = LBound(rows_array, 1) To UBound(rows_array, 1) For j = LBound(dr(1), 1) To UBound(dr(1), 1) 'through each field based on row 1 ' Debug.Print dr(i)(j) dr_Proper(i, j) = dr(i)(j) Next j Next i 'needs activesheet(Sorry) drops at end! sCorner.Select '''StretchDataRange.Value = dr DropitoutISS dr_Proper, sCorner.Parent, sCorner End Sub Sub DropitoutISS(outvar(), outSheet As Worksheet, ohome As Range) '2 dim only 'zero based version Dim outzap As Variant ' array holder 'ur = realRows(outSheet) + 2 'Set ohome = outSheet.Cells(ur, 1) LR = UBound(outvar, 1) + 1 lc = UBound(outvar, 2) + 1 'create a variant array of right size on ohome with current sheet contents outzap = ohome.Range(Cells(1, 1), Cells(LR, lc)).Value 'load variant with ov values For j = 1 To lc ' now ZAP index variable For i = 1 To LR outzap(i, j) = outvar(i - 1, j - 1) 'expects 0 at start of array Next i Next j ''zap!!! overwrite that sheet range ohome.Range(Cells(1, 1), Cells(LR, lc)).Value = outzap End Sub Sub datesTidy(GotdatesRange As Range) Dim C As Range 'Cell in fact For Each C In GotdatesRange If IsDate(C.Value) Then C.Value = CDate(C.Value) End If Next C End Sub Sub ConcComp(dataRange As Range, destCell As Range) '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> _ 06-Dec-12 '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> destCell = squashData(dataRange) End Sub ' ConcComm Sub ConcINflate(sqdata As String, StretchDataRange As Range) 'wrappers for the comm handlig stuff '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> _ 06-Dec-12 '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> StretchData sqdata, StretchDataRange.Cells(1, 1) ' ????????????????????datesTidy Selection Range End Sub ' ConcComm Sub rangeTOpsv(inputRange As Range, ffn As String) 'Chris ::put header info here Dim oBuf As Variant, outString As String, SEP As String SEP = "," Open ffn For Output As #1 Print #1, Now oBuf = inputRange.Value For i = LBound(oBuf, 1) To UBound(oBuf, 1) outString = oBuf(i, 1) For j = LBound(oBuf, 2) + 1 To UBound(oBuf, 2) outString = outString & SEP & oBuf(i, j) Next j Print #1, outString Debug.Print outString Next i ' Close before reopening in another mode. Close #1 End Sub Sub Irritate1() '' Macro2 Macro '' Workbooks.Add Columns("A:A").ColumnWidth = 64.22 Rows("1:1").RowHeight = 181.6 Columns("B:B").Select Range(Selection, Selection.End(xlToRight)).Select Selection.EntireColumn.Hidden = True Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Hidden = True Range("A1").Select For i = 1 To 56 With Selection.Interior .Pattern = xlSolid .ColorIndex = i End With gasp Next i With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10498160 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With ActiveWorkbook.Close False End Sub Sub i2() For i = 1 To 3 Irritate1 ulog Application.StatusBar & "I-End " & i & " " & Now() Next End Sub Sub form_trans1() 'Moves forulae LITERALLY and LAterally but does work Dim fRange As Range, fBuf As Variant Set fRange = Selection With fRange fBuf = fRange.Formula fRange.Offset(0, 2).Formula = fBuf End With End Sub Sub form_trans2Sieves() 'Moves forulae LITERALLY and LAterally but does work 'throws out Dim fRange As Range, fBuf As Variant, j As Integer Set fRange = Selection With fRange fBuf = fRange.Formula For j = LBound(fBuf, 1) + 1 To UBound(fBuf, 1) Next j fRange.Offset(0, 2).Formula = fBuf End With End Sub Sub Zs2Null() 'MAKE SURE RIGHT SHEET IS SELECTED For Each bc In [w5:w1377] 'Selection If Len(bc.Value) = 0 Then bc.ClearContents Next End Sub Function blankcell() Application.Volatile 'blankcell = "" ThisCell.ClearContents End Function Sub logMaxima() ' ' Macro2 Macro ' Dim logMaxima(1 To 1000, 1 To 2) 'the hanging ,10 lets me paste the array straight in to a vertical bunch of cells 'Cw wasteful of logmx 'needs redim from LB Dim LB As Variant, lmaxpos As Integer, TempTimeStamp As Variant, LBi As Integer, temPdate As Variant lmaxpos = 1 ' ActiveWindow.ScrollWorkbookTabs Position:=xlLast LBi = 1 With ThisWorkbook.Sheets("logsheet") .Activate 'fill array LB = .Range(.[A5].CurrentRegion.Address & " a:b").Value temPdate = LB(LBi, 1): TempTimeStamp = LB(LBi, 2) For LBi = LBound(LB, 1) To UBound(LB, 1) 'run down list comparing with last values 'List is expected to be sorted ..? If temPdate = LB(LBi, 1) Then 'same date 'no need to change tempdate but 'test tts should be bigger & 'replace If TempTimeStamp < LB(LBi, 2) Then TempTimeStamp = LB(LBi, 2) Else 'new date 'load td and tts into the LogMaxima array logMaxima(lmaxpos, 1) = temPdate logMaxima(lmaxpos, 2) = TempTimeStamp lmaxpos = lmaxpos + 1 'loadup new temp variable temPdate = LB(LBi, 1): TempTimeStamp = LB(LBi, 2) End If Next LBi End With Sheets.Add [a1:b1000].Value = logMaxima AddCol End Sub Sub timesClearAll() [up_times].ClearContents End Sub Sub addsVARILINK_P_ISS() ' ' Macro1 Macro'used in KCMT leaveplanner ... ' ' Range("d70").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "END_Calendar", TextToDisplay:="END_Calendar" End Sub Sub KCMTConst2() ' added 2000 columns by accident ' Macro2 Macro Exit Sub Windows("Managment Team Leave Planner.xlsx").Activate [w12].Select For i = 1 To 1000 Columns("Z:Z").Delete Shift:=xlToLeft If Len([aa6]) > 0 Then Exit For Next [u1] = i [G12].Select End Sub Sub KCMTConst3() Set firstsrange = Selection For Each C In firstsrange Range(C.Value).Select Breather 1 Next End Sub Sub KCMTConst4() Set firstsrange = Selection For Each C In firstsrange C.Interior.ColorIndex = Range(C.Value).Interior.ColorIndex C.Font.ColorIndex = Range(C.Value).Font.ColorIndex Breather 1 Next End Sub Sub KCMTConst6() Set DateRange = [w8:rr8] ''Dim fTarg As Range For Each C In DateRange If Weekday(C.Value) = 7 Or Weekday(C.Value) = 1 Then C.Select C.Font.ColorIndex = 3 C.Offset(-1, 0).Font.ColorIndex = 3 'Breather 1 End If Next End Sub Sub KCMTConst5() Set firstsrange = Selection Dim fTarg As Range For Each C In firstsrange Set fTarg = Range(C.Value) fTarg.Select fTarg.Offset(-4, 0) = Format(fTarg.Value, " mmmm yyyy") 'grab month heding Set fTarg = fTarg.Offset(-4, 0) 'shift to new cell .. Range(fTarg, fTarg.Offset(0, 2)).Merge Range(fTarg, fTarg.Offset(0, 2)).Style = "Heading 3" Breather 1 Next End Sub Sub script_backups() 'MsgBox "bbi working" ThisWorkbook.Save Biss_backup 'C_Biss_backup End Sub Sub Biss_backup() ''''''testing '''''''''On Error GoTo noFile: Dim strf As String Set fs = CreateObject("Scripting.FileSystemObject") Set FL = ThisWorkbook.Sheets("macrodata") With FL Set tbfcell = .[d17:d1000].Find("users$\ChWright") ' tbfcell.Select dest_stem = tbfcell.Value sourcefileName = ThisWorkbook.FullName fs.copyfile sourcefileName, dest_stem strf = "copied " & sourcefileName & " to " & dest_stem ulog strf fs.copyfile sourcefileName, sourcefileName & ".bissb" strf = "copied " & sourcefileName & " to " & sourcefileName & ".bissb" ulog strf If Weekday(Now()) = 2 Then fs.copyfile sourcefileName, sourcefileName & ".bissb_Monday" strf = "copied " & sourcefileName & " to " & sourcefileName & ".bissb_Monday" ulog strf End If End With Set fs = Nothing noFile: End Sub Function czod(chin_year As Integer) As String Dim CZ As Variant CZ = Array("Rat", "Ox", "Tiger", "Rabbit", "Dragon", "Snake", "Horse", "Goat", "Monkey", "Rooster", "Dog", "Pig") Const CZ_shift As Integer = 4 Dim cpos As Integer czod = CZ((chin_year - CZ_shift) Mod 12) End Function Sub JustGetTimeswkbk() Dim tbfcell As Range, timesBook As Workbook umacsheet = "macrodata" Debug.Print Now() Set tbfcell = ThisWorkbook.Sheets(umacsheet).[b17:b1000].Find("times2013") Set timesBook = Workbooks.Open(tbfcell.Value, , , , "apex") End Sub Sub tester__P_ISS_ALL() 'findDate(#1/1/2013#, Selection).Select 'Dim string_dum As String 'vshort gap 'D'ebug.Print czod(1964) 'If ThisWorkbook.Name = ActiveWorkbook.Name Then Exit Sub 'MsgBox (squashData(Selection)) ''[h1].Value = squashData([b2].CurrentRegion) 'StretchData [h1].Value, Selection ' [r1:t3] 'datesTidy Selection ''rangeTOpsv Selection, ActiveWorkbook.FullName & ".csv" ''findALL_WV("0", Selection).Select ''Findfixer End Sub Sub payFeb2_d1() Dim cbook As Workbook Dim dBook As Workbook Dim cmatRange As Range Dim dmatRange As Range Application.ScreenUpdating = False Set dBook = Workbooks.Open(Filename:= _ "\\issukacpfil001\offlinefiles$\chwright\Desktop\workISS\PayrollFEB\CW 21Feb - Barclays Cleaning Hourly Employee VER 2 .xlsx") ' Set cbook = Workbooks.Open(Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\PayrollFEB\temp2.xlsx") ' Application.ScreenUpdating = False 'ange("L3:L23").Select Set cmatRange = cbook.Worksheets("Cleaning Matches").[k2:k2624] Set dmatRange = dBook.Worksheets("Barclay's Cleaning HE").[a6:a3040] ' 25 2 2013 ' needs KNITTED HALF RECORDS NO 'the sorts were messed up so 'matched in the k column ulog "payfeb running" For Each C In cmatRange Set d = Nothing Set d = dmatRange.Find(C.Value, , , xlWhole) If d Is Nothing Then C.Offset(0, -1).Value = " NO Match" Else 'fun goes in here Set dRange = d.Range("a1:n1") dRange.Copy Destination:=C.Offset(0, 1) C.Offset(0, -1).Value = "Matched" End If If Right(C.Row, 2) = "00" Then Application.ScreenUpdating = True ulog C.Value & " row " & C.Row Application.ScreenUpdating = False End If Next C dBook.Close False Application.ScreenUpdating = True Findfixer ulog "payfeb2 ends" End Sub Sub payFeb2_d2() Dim cbook As Workbook Dim dBook As Workbook Dim cmatRange As Range Dim dmatRange As Range Set cbook = Workbooks.Open(Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\PayrollFEB\CW 8 - Monthly Project Code.xlsx") ' Set dBook = Workbooks.Open(Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\PayrollFEB\CW 9 - Salaried Employee File Matrix.xlsx") ' Application.ScreenUpdating = False 'ange("L3:L23").Select Set cmatRange = cbook.Worksheets("Employee List").[k2:k830] Set dmatRange = dBook.Worksheets("Salaried").[a6:a900] ' 25 2 2013 ' needs KNITTED HALF RECORDS NO 'the sorts were messed up so 'matched in the k column ulog "payfeb running : day 2" For Each C In cmatRange Set d = Nothing Set d = dmatRange.Find(C.Value, , , xlWhole) If d Is Nothing Then C.Offset(0, -1).Value = " NO Match" Else 'fun goes in here Set dRange = d.Range("a1:n1") dRange.Copy Destination:=C.Offset(0, 1) C.Offset(0, -1).Value = "Matched" End If If Right(C.Row, 2) = "00" Then Application.ScreenUpdating = True ulog C.Value & " row " & C.Row Application.ScreenUpdating = False End If Next C dBook.Close False Application.ScreenUpdating = True Findfixer ulog "payfeb2 ends" End Sub Sub namestest1() 'killing duff links '''''''''''''''''''''by deleting the old names Dim N As Name, NRange As Range '' ''Searchstr = "#REF!" ' '' ''Set all_names = ActiveWorkbook.Names ''For Each n In all_names '' If InStr(1, n.RefersTo, Searchstr, 1) > 0 Then '' n.Delete '' End If ''Next n Workbooks.Add ActiveSheet.Name = "name infested" For i = 1 To 10 '000 'Set n = ActiveWorkbook.Names.Add( _ ' Name:="Flea_" & "A" & i, RefersTo:="=" & "$A$" & i) Debug.Print i Set NRange = Cells(i, 1) Set N = ActiveWorkbook.Names.Add( _ Name:="Flea_" & dollarOut(NRange.Address), RefersTo:="=" & NRange.Address) Next End Sub Sub collage__ClogS() Const CORNER As String = "Type of Call received" Dim FfilelistRange As Range, fc As Range, tcornerRange As Range Dim colSh As Worksheet, s As Worksheet Dim PastePos As Integer ulog "clog2 start" Dim ShiftRow_int As Integer '= 9 'Range("Q9").= "shift row" Dim TypeCol_int As Integer ' = 1 'Range("A47").Selection.FormulaR1C1 = "tyeps of call" Set FfilelistRange = ThisWorkbook.Worksheets("CLOGS").Range("A2:A44") Set colSh = ThisWorkbook.Worksheets("CLOGS DATA") PastePos = 1 colSh.Cells.Clear With colSh .Cells(PastePos, 1).Value = "date here" .Cells(PastePos, 2).Value = "day here" .Cells(PastePos, 3).Value = "bring this code" .Cells(PastePos, 4).Value = "calls on sec log here" .Cells(PastePos, 5).Value = "Call type" .Cells(PastePos, 6).Value = "Hour of Shift" .Cells(PastePos, 7).Value = "number of call" .Cells(PastePos, 8).Value = "Filename " End With PastePos = PastePos + 1 'top loop For Each fc In FfilelistRange Set ClogBook = Workbooks.Open(fc.Value, 0, True) ulog fc.Offset(0, 5).Value Set s = ClogBook.Worksheets("Sheet1") Set tcornerRange = s.Cells.Find(CORNER) TypeCol_int = tcornerRange.Column ShiftRow_int = tcornerRange.Row ' Debug.Print [A2] ' = "date here" ' Debug.Print [B2] '= "day here" ' Debug.Print [N2] '= "bring this code" ' Debug.Print [B6] '= "calls on sec log here" ' vvvvvvvvvvvvvvvv data range here vvvvvvvvvv For Each C In s.Range(tcornerRange.Offset(1, 1), tcornerRange.Offset(40, 12)).SpecialCells(xlCellTypeConstants, 23) 'Debug.Print c.Value; ","; 'Debug.Print Cells(c.Row, TypeCol_int).Value; ","; 'Debug.Print Cells(ShiftRow_int, c.Column); 'Debug.Print With colSh .Cells(PastePos, 1).Value = tcornerRange.Offset(-7, 0) ' = "date here" .Cells(PastePos, 2).Value = tcornerRange.Offset(-7, 1) '= "day here" .Cells(PastePos, 3).Value = tcornerRange.Offset(-7, 13) '= "bring this code" .Cells(PastePos, 4).Value = tcornerRange.Offset(-3, 1) '= "calls on sec log here" .Cells(PastePos, 5).Value = s.Cells(C.Row, TypeCol_int).Value .Cells(PastePos, 6).Value = s.Cells(ShiftRow_int, C.Column) .Cells(PastePos, 7).Value = C.Value .Cells(PastePos, 8).Value = fc.Value End With PastePos = PastePos + 1 Next C ClogBook.Close False Next fc ThisWorkbook.Save ulog "clog2 finish" '' PastePos = colSh.UsedRange.Columns.Count + 1 '' colSh.Rows("1:6").UnMerge '' s.UsedRange.Copy colSh.Cells(1, PastePos) '' colSh.Cells(1, PastePos + 1).Value = fc.Offset(0, 1).Value '' test For Each s In ClogBook.Worksheets '' Debug.Print s.Name; " "; '' Debug.Print WorksheetFunction.CountA(Cells); " consts "; '' Debug.Print ","; '' Next s '' Debug.Print End Sub Sub dumpprojectDATA_antcp() Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Dim allad As String ulog "datadumper PD" Set projSheet = projBook.Sheets("ISS Projects & MAC Live Tracker") Set outSheet = projBook.Sheets("DataDump") outSheet.Activate [a1].Select Cells.ClearContents allad = "a5:a1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "bg5:bg1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "cb5:cb1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "cu4:dg1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad For Each C In Range(allad) If C.Value = 0 Then C.ClearContents Next C [5:5].SpecialCells(xlCellTypeBlanks).EntireColumn.Delete [a1].Select Application.ScreenUpdating = True ulog "finished" End Sub Sub dumpprojectDATA_actual() 'methodical extraction from the Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Dim allad As String ulog "datadumper PD" Set projSheet = projBook.Sheets("ISS Projects & MAC Live Tracker") Set outSheet = projBook.Sheets("DataDump") outSheet.Activate [a1].Select Cells.ClearContents allad = "a5:a1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "bl5:bl1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "cl5:cl1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "di1:du1007" Range(allad).Value = projSheet.Range(allad).Value ulog allad allad = "di4:du1007" findALL_WV("0", Range(allad)).ClearContents 'clear out 0 s Findfixer [5:5].SpecialCells(xlCellTypeBlanks).EntireColumn.Delete [a1].Select Application.ScreenUpdating = True ulog "finished" End Sub Sub sparse2listGEN() Dim sparBook As Workbook Dim sparSheet As Worksheet, outSheet As Worksheet Dim sparRange As Range, sparseOnlyRange As Range Dim refCOL_range As Range 'multi column Dim refROW_range As Range 'multi ROW referenc data that we need Dim C As Range, d As Range Dim outcount As Integer, outCOLL As Integer SDeleter outcount = 0 Set sparBook = ActiveWorkbook Set outSheet = sparBook.Sheets.Add Set sparSheet = sparBook.Sheets("DataDump") 'form tidy records fro selected cells in spars range eg. to send to MSaccess 'set Ranges Set sparRange = Nothing Set refCOL_range = Nothing 'multi column Set refROW_range = Nothing 'multi ROW referenc data that we need ''Set sparRange = sparSheet?? ''Set refcol_range = sparSheet?? 'multi column ''Set refcol_range = sparSheet?? 'multi ROW referenc data that we need Set sparRange = sparSheet.[e6:p1007] Set refCOL_range = sparSheet.[a:d] 'multi column proj/ comp Set refROW_range = sparSheet.[4:5] 'date '''too specific sparRange.findall("0" ... Set sparseOnlyRange = sparRange.SpecialCells(xlCellTypeConstants, 23) 'CHANGE THIS SELECTIO CRITERIon 'sparseOnlyRange.Select For Each C In sparseOnlyRange outcount = outcount + 1 outCOLL = 1 'ref data in columns For Each d In Intersect(C.EntireRow, refCOL_range) outSheet.Cells(outcount, outCOLL) = d.Value outCOLL = outCOLL + 1 Next d 'ref data in rows For Each d In Intersect(C.EntireColumn, refROW_range) outSheet.Cells(outcount, outCOLL) = d.Value outCOLL = outCOLL + 1 Next d outSheet.Cells(outcount, outCOLL) = C.Value Next C End Sub Sub clogcleaner1() 'just leave term after last space 'pos convert to date 'cf afterchar On Error Resume Next For Each C In Selection v = Split(C.Value) C.Value = CDate(v(UBound(v))) ' c.Value = v(UBound(v)) Next C End Sub Sub Macro4() ' Cells.Select ' Cells.EntireRow.AutoFit End Sub Sub Access_F_TYPES() '' projects acc db' ' end 08/03/2013 16:11:00 Sheets("Meta data Breakdown").Select With [p4:p200].Offset(0, 1) .Value = [h4:h200].Offset(0, 1).Value .Item(1).Value = "Type_of_field" .Replace What:=" ", Replacement:="", LookAt:=xlPart .Replace What:="int", Replacement:="integer", LookAt:=xlPart .Replace What:="string", Replacement:="varchar(", LookAt:=xlPart End With For Each C In [p4:p200].Offset(0, 1) If C.Value Like "*(*" Then C.Value = C.Value & ")" Next Range("Q1").FormulaR1C1 = "end" & Now Range("q4").Select End Sub Sub Access_F_SQL_collect() '' projects acc db' ' end 08/03/2013 16:11:00 Dim SQL_text As String Sheets("Meta data Breakdown").Select SQL_text = "" For Each C In [p4:p200].Offset(0, 1) If Len(C.Value) > 0 Then SQL_text = SQL_text & vbCrLf & _ C.Offset(0, -1).Value & " " & C.Value & "," End If Next Range("Q1").FormulaR1C1 = "end" & Now Range("p1").Select Range("r1").Value = SQL_text End Sub Sub b2trim() Dim buf As Variant 'Get values as an array buf = Selection.Value For j = LBound(buf, 2) To UBound(buf, 2) For i = LBound(buf, 1) To UBound(buf, 1) '''''''''''f Len(Buf(i, j)) = 0 Then Buf(i, j) = Null buf(i, j) = Trim(buf(i, j)) Next i Application.StatusBar = j Next j 'Put the array back into the worksheet 'Clobbers formulae! Selection.Value = buf End Sub Sub comments_proj() '' projects acc db' '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> _ 13-Mar-13 W @ H '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Dim data_imp_Sheet As Worksheet Dim comments_Sheet As Worksheet SDeleter Set comments_Sheet = Worksheets.Add Set data_imp_Sheet = Sheets("import") For Each C In data_imp_Sheet.[a2:a1000] If Len(C.Value) Then crap = crap + 1 ' Debug.Print crap; " "; c.Value comments_Sheet.Cells(crap, 1) = C.Value comments_Sheet.Cells(crap, 2) = C.Offset(0, 70).Value End If Next Debug.Print crap Debug.Print "end" & Now End Sub Sub comments_proj2() If Range("a1").Value <> "ISS_Projects_URN" Then Exit Sub Set d = [f1] d.CurrentRegion.Clear For Each C In Range("B2:B1600").SpecialCells(xlCellTypeConstants, 23) clines = Split(C.Value, Chr(10)) For i = LBound(clines, 1) To UBound(clines, 1) Set d = d.Offset(i, 0) d.Value = clines(i) d.Offset(0, -1).Value = C.Offset(0, -1).Value Next i Next C End Sub '' '' '' Sub copyingBND() '... to new areas Dim bndTbook As Workbook Dim bndOUTsheet As Worksheet Dim FilesSheet As Worksheet Dim FCell As Range, Sitec As Range Dim rEp As Integer Dim bo_row_int As Integer Dim BndFileName As String bo_row_int = 0 Dim tunumber As String, firstAddress As String, line As String, SP As String SP = "," Set FilesSheet = ThisWorkbook.Sheets("BND Files") Set bndOUTsheet = ThisWorkbook.Sheets("BND_copy_list") Set bndTbook = Workbooks.Open(Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\BND Project\CUT DOWN FEB 13 (2007 & 2008 Only).xlsx" _ , UpdateLinks:=0) Sheets("Network Delivery Plan").Select 'keep this active For Each Sitec In [f6:f330] bo_row_int = bo_row_int + 1 BndFileName = "" line = "" line = line & SP & Sitec.Value line = line & SP & Sitec.Address line = line & SP & Sitec.Offset(0, 2).Value line = line & SP & Sitec.Offset(0, 9).Value line = line & SP & Sitec.Offset(0, 10).Value tunumber = "TU" & Sitec.Value Set FCell = Nothing Set FCell = FilesSheet.[A:A].Find(What:=tunumber, After:=FilesSheet.[a1], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If FCell Is Nothing Then BndFileName = " no file found" line = line & SP & BndFileName Debug.Print line bndOUTsheet.Cells(bo_row_int, 1).Value = line Else firstAddress = FCell.Address rEp = 0 Do rEp = rEp + 1 bo_row_int = bo_row_int + 1 BndFileName = FCell.Value Set FCell = FilesSheet.Columns("A:A").Cells.FindNext(FCell) Debug.Print line; SP; BndFileName; SP; rEp bndOUTsheet.Cells(bo_row_int, 1).Value = line & SP & BndFileName & SP & rEp Loop While Not FCell Is Nothing And FCell.Address <> firstAddress 'catching later files End If Next Sitec bndTbook.Close False ThisWorkbook.Save End Sub Sub text2column() topcol = 28 For Each C In [a:w].SpecialCells(xlCellTypeConstants) topRow = 1 + topRow Cells(topRow, topcol).Value = C.Value Next C End Sub Sub column2processBoxes() ' ' Macro2 Macro ' Windows("process chart 2.xlsx").Activate Sheets("comparison").Select [g1].Select Set sh = Sheets("comparison").Shapes.AddShape(msoShapeFlowchartProcess, 55.2174015748, _ 6.5217322835, 78.2608661417, 50.8696062992) ' Sh.ShapeStyle = msoShapeStylePreset5 sh.ShapeStyle = msoShapeStylePreset2 ''''''' ''''''' ''''''' ''''''' ''''''' ''''''' range("Z1").Select ''''''' ActiveSheet.Shapes.range(Array("Flowchart: Process 2")).Select ''''''' Selection.Formula = "=Z1" ''''''' range("AC6").Select ''''''' ActiveSheet.Shapes.range(Array("Flowchart: Process 1")).Select ''''''' Selection.ShapeRange ''''''' Selection.ShapeRange.IncrementTop 70 ''''''' range("AD9").Select ''''''' ActiveSheet.Shapes.range(Array("Flowchart: Process 38")).Select ''''''' Selection.Formula = "=Z6" ''''''' range("AD9").Select ''''''' ActiveSheet.Shapes.range(Array("Flowchart: Process 38")).Select ''''''' Selection.Delete ''''''' ActiveSheet.Shapes.range(Array("Flowchart: Process 1")).Select ''''''' Selection.Delete ''''''' range("AA4").SelectAutomated Process"Sheets("Current process") '''''''End Su For Each C In Sheets("Automated Process").[z1:z100].SpecialCells(xlCellTypeConstants) sh.TextFrame.Characters.Text = C.Value Set sh = sh.Duplicate sh.IncrementTop -12 sh.IncrementLeft 100 Next C End Sub Sub ScansFiles___loads_of_them() 'rflag4 use this one Chris 'do they exist? Dim acsBook As Workbook Dim Filename As String ulog "Start : fileScan" On Error GoTo handleError [a2:a334].Select Set c22namerange = Selection '[C198:D347] numCfiles = c22namerange.Rows.Count errcount = 0 For ri = 1 To numCfiles Err.Clear Filename = c22namerange.Cells(ri, 1).Value 'Debug.Print fileName & vbCrLf & newfilename 'correct method! Set acsBook = Workbooks.Open(Filename, 0) If Not acsBook Is Nothing Then acsBook.Close False ulog Filename & Err.Description Next MsgBox "finished " & numCfiles & " files" & vbCrLf & errcount & " errors" ulog "Finish : fileScan" Exit Sub handleError: errcount = errcount + 1 Select Case Err Case 53 ulog "file not found " & newfilename Resume Next Case 58 'already got it ulog "file already there " & newfilename Resume Next Case Else ulog Err.Description Resume Next End Select End Sub Sub copycolours() Dim C As Range, b As Range For Each C In Selection Set b = Cells(C.Row, 2) b.Copy C.PasteSpecial xlPasteFormats Next End Sub Sub callahan_test_data() ' 09/04/2013 ' CDSM Macro 'callahan test data saved here Application.ScreenUpdating = False Workbooks.Add Sheets.Add ActiveSheet.Name = "Callahan" [a1] = "FirstName" [A10].Value = "Rob" [A11].Value = "Michael" [A12].Value = "Kevin" [A13].Value = "Maura" [A14].Value = "Steve" [A15].Value = "Kara" [A16].Value = "Deborah" [A17].Value = "Julie" [A18].Value = "Paul" [A19].Value = "Charles" [a2].Value = "Janée" [a3].Value = "Bonnie" [a4].Value = "Darren" [A5].Value = "Judy" [A6].Value = "Kevin" [A7].Value = "Gilbert" [A8].Value = "Hardy" [A9].Value = "David" [B1].Value = "LastName" [B10].Value = "MacDougall" [B11].Value = "Mahoney" [B12].Value = "O'Halloran" [B13].Value = "O'Keefe" [B14].Value = "Saunders" [B15].Value = "Stepanian" [B16].Value = "Sundberg" [B17].Value = "Treece" [B18].Value = "Vomund" [B19].Value = "Willmes" [b2].Value = "Baugher" [b3].Value = "Christensen" [b4].Value = "Demerais" [B5].Value = "Doyle" [B6].Value = "Geloff" [B7].Value = "Greenman" [B8].Value = "Griffin" [B9].Value = "Hunting" [c1].Value = "CompanyName" [C10].Value = "" [C11].Value = "Design Specialists" [c12].Value = "Editorial Consultants" [C13].Value = "Helping Hand" [C14].Value = "" [c15].Value = "Big Bad Books" [c16].Value = "Scissor's Edge" [C17].Value = "" [c18].Value = "Snowboard Central" [C19].Value = "" [c2].Value = "Baugher Biotech" [c3].Value = "Dance Academy" [c4].Value = "L'École Française" [C5].Value = "Paper Dolls Interiors" [c6].Value = "Ballard Academy" [C7].Value = "Greenman, Delp & Whitney" [C8].Value = "Ballard Brewery" [c9].Value = "Software Solutions" [D1].Value = "Title" [D10].Value = "" [D11].Value = "" [D12].Value = "Editor" [D13].Value = "Director" [D14].Value = "" [D15].Value = "Publisher" [D16].Value = "Owner" [D17].Value = "" [D18].Value = "Sales Representative" [D19].Value = "" [D2].Value = "Owner" [d3].Value = "" [d4].Value = "Instructor" [d5].Value = "Owner" [D6].Value = "Principal" [D7].Value = "Attorney" [D8].Value = "Master Brewer" [D9].Value = "Consultant" [E1].Value = "Address" [E10].Value = "4550 W. Jefferson Blvd." [E11].Value = "3300 Colorado Blvd." [E12].Value = "300 Park Avenue #1301" [E13].Value = "31 E. Houston St. #6" [E14].Value = "3408 S.E. 11th Ave." [E15].Value = "480 Commonwealth Ave." [E16].Value = "1005 Peachtree St. N.E." [E17].Value = "835 E. Prospect" [E18].Value = "22 Market St." [E19].Value = "27 Christopher St." [e2].Value = "220 Newbury St." [E3].Value = "4220 Main St." [e4].Value = "1020 Ranier Ave. S." [E5].Value = "472 Lexington Ave." [E6].Value = "2312 N.W. 99th St." [E7].Value = "23 W. 48th St. #2" [E8].Value = "115 Leary Wy." [E9].Value = "39 E. 64th St." [f1].Value = "City" [F10].Value = "Los Angeles" [F11].Value = "Denver" [F12].Value = "New York" [F13].Value = "New York" [F14].Value = "Portland" [F15].Value = "Boston" [F16].Value = "Atlanta" [F17].Value = "Seattle" [F18].Value = "San Francisco" [f2].Value = "Boston" [F20].Value = "" [F21].Value = "CNW " & Now [F3].Value = "Dallas" [F4].Value = "Seattle" [F5].Value = "New York" [F6].Value = "Seattle" [F7].Value = "New York" [F8].Value = "Seattle" [F9].Value = "New York" [g1].Value = "StateOrProvince" [G10].Value = "CA" [G11].Value = "CO" [G12].Value = "NY" [G13].Value = "NY" [G14].Value = "OR" [G15].Value = "MA" [G16].Value = "GA" [G17].Value = "WA" [G18].Value = "CA" [G19].Value = "NY" [G2].Value = "MA" [G3].Value = "TX" [G4].Value = "WA" [G5].Value = "NY" [G6].Value = "WA" [G7].Value = "NY" [G8].Value = "WA" [G9].Value = "NY" [h1].Value = "PostalCode" [h1].Value = "PostalCode" [H10].Value = "90016" [H11].Value = "80205" [H12].Value = "" [H13].Value = "10012" [H14].Value = "97202" [H15].Value = "2215" [H16].Value = "30309" [H17].Value = "98102" [H18].Value = "94111" [H19].Value = "10014" [H2].Value = "2116" [H3].Value = "75226" [H4].Value = "98144" [h5].Value = "10017" [H6].Value = "98117" [H7].Value = "10020" [H8].Value = "98115" [H9].Value = "10021" [i1].Value = "WorkPhone" [I10].Value = "2135558221" [I11].Value = "3035551108" [I12].Value = "2125552342" [I13].Value = "2125557154" [I14].Value = "5035558322" [I15].Value = "6175555452" [I16].Value = "4045556000" [I17].Value = "2065556068" [I18].Value = "4155553244" [I19].Value = "2125552219" Range("i2").Value = "6175552212" [I3].Value = "2145555541" [I4].Value = "2065557788" [I5].Value = "2125554220" [I6].Value = "2065556068" [I7].Value = "2125554400" [I8].Value = "2065555317" [i9].Value = "2125557880" Range("A1").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$19"), , xlYes).Name = _ "Table1" [a1].CurrentRegion.Select Application.ScreenUpdating = False End Sub Function findALL_WV(searchTerm As Variant, Fir As Range) As Range 'returns a range with containing all sucessful matches. 'example _ 'This example finds all cells in the worksheet one that _ c-o-n-t-a-i-n the value searchterm and add to findall region Set findALL_WV = Nothing With Fir 'parameterised for ranges. Set C = .Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlWhole) If Not C Is Nothing Then firstAddress = C.Address Set findALL_WV = C Do Set findALL_WV = Application.Union(findALL_WV, C) Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address <> firstAddress End If End With End Function Sub addadd() For Each C In Selection C.Value = C.Address Next End Sub Sub WeeklyUpdateFun() ' ' Macro2 Macro ' lastfri = Date - Weekday(Date) - 1 'previous Friday Workbooks.Open Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\Station7\Weekly Update report WeekEnding " & Format(lastfri - 7, "ddmmyyyy") & ".xlsx" Range("D1").Value = Now [c4].Value = lastfri 'previous Friday ActiveSheet.Name = "Update W E " & Format(lastfri, "dd.mmm") ActiveWorkbook.SaveAs Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\Station7\Weekly Update report WeekEnding " & Format(lastfri, "ddmmyyyy") & ".xlsx" [g2:g50].Value = [e2:e50].Value [e2:e50].ClearContents Exit Sub Range("C12").Select Selection.AutoFill Destination:=Range("C12:C13"), Type:=xlFillFormats Range("C12:C13").Select Range("C16").Select ActiveCell.FormulaR1C1 = "new data sourse rceived " Range("C18").Select Selection.ClearContents Range("C9").Select Selection.AutoFill Destination:=Range("C9:C13"), Type:=xlFillFormats Range("C9:C13").Select Range("C13").Select ActiveWindow.SmallScroll Down:=6 Range("C22").Select Selection.ClearContents Range("C20").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=3 Range("B19:B21").Select Selection.CheckSpelling SpellLang:=2057 ActiveWindow.SmallScroll Down:=-6 Range("C10:C19").Select Selection.CheckSpelling SpellLang:=2057 Range("C19").Select ActiveWindow.SmallScroll Down:=3 Range("C20").Select ActiveCell.FormulaR1C1 = "PD user layer finished" Range("C18").Select ActiveCell.FormulaR1C1 = "ACS still " Range("C20").Select ActiveCell.FormulaR1C1 = "PD user layer finished" Range("C18").Select ActiveCell.FormulaR1C1 = "ACS still on hold" Range("C20").Select Selection.AutoFill Destination:=Range("C20:C21"), Type:=xlFillDefault Range("C20:C21").Select Range("C21").Select ActiveCell.FormulaR1C1 = "create first Survey pack CN/JW" Range("D12").Select ActiveWindow.SmallScroll Down:=-18 Range("A1:C1").Select ActiveCell.FormulaR1C1 = "Chris Wright Workload Report" Range("C4").Select ActiveCell.FormulaR1C1 = "4/12/2013" Range("E1").Select ActiveCell.FormulaR1C1 = "14:04" Range("E2").Select ActiveWorkbook.Save ActiveWorkbook.SaveAs Filename:= _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\Station7\Weekly Update report WeekEnding 12042013.xlsx" _ , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Range("C6").Select ActiveCell.FormulaR1C1 = "structure relationships amended" Range("C7").Select ActiveCell.FormulaR1C1 = "visual basic modules created" Range("C8").Select ActiveCell.FormulaR1C1 = "basic report created" Range("C9").Select ActiveCell.FormulaR1C1 = "Database to v 1.16" Range("C10").Select ActiveCell.FormulaR1C1 = "Database main form updated" Range("C11").Select ActiveCell.FormulaR1C1 = "Tech call to Projects team Outstanding" Range("C12").Select ActiveCell.FormulaR1C1 = "meeting with commercial manager" Range("C13").Select ActiveCell.FormulaR1C1 = "" Range("C14").Select ActiveCell.FormulaR1C1 = "surveys/ACS" Range("C15").Select ActiveCell.FormulaR1C1 = "visual basic routines started" Range("C16").Select ActiveCell.FormulaR1C1 = "new data source received " Range("C17").Select ActiveCell.FormulaR1C1 = "liaison with surveyor /retail" Range("C18").Select ActiveCell.FormulaR1C1 = "ACS still on hold" Range("C19").Select ActiveCell.FormulaR1C1 = "" Range("C20").Select ActiveCell.FormulaR1C1 = "PD user layer finished" Range("C21").Select ActiveCell.FormulaR1C1 = "create first Survey pack CN/JW" Range("C22").Select ActiveCell.FormulaR1C1 = "" Range("D6").Select ActiveWorkbook.Save ActiveWindow.Close Application.WindowState = xlMinimized Application.WindowState = xlNormal End Sub Sub addressSPsplitter() Set C = ActiveCell C.Offset(0, 1) = " " & beforeCharNEW("" & C.Value, " ") C.Offset(0, 2) = bigtailNEW("" & C.Value, " ") C.Offset(1, 0).Select End Sub Sub dealLines2013() '? format(now(),"dddd dd mmmm yyyy") 'Tuesday 23 April 2013 Const COLCHECK = 1 ' MUST BE SORTED BY THIS COLUMN ' MUST BE SORTED BY THIS COLUMN ' MUST BE SORTED BY THIS COLUMN Dim dealBook As Workbook Dim dealSheet As Worksheet Dim shareSheet As Worksheet Dim title_address As String Dim sRange As Range, C As Range, title_range As Range Dim D__range As Range Dim rCount As Integer Application.ScreenUpdating = False If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub Set dealBook = ActiveWorkbook Set dealSheet = ActiveSheet Set title_range = Intersect(dealSheet.UsedRange, dealSheet.[1:1]) ''''must be row1! For Each C In Intersect(dealSheet.Columns(COLCHECK), dealSheet.UsedRange, dealSheet.[2:60000]) 'main column,usedrange and no first row ... Set sRange = Intersect(dealSheet.UsedRange, C.EntireRow) 'c's data row/LINE ... If Len([C].Value) = 0 Then Exit For If [C].Value = [C].Offset(-1, 0) Then 'same sort of record same sheet record:new sheet rCount = rCount + 1 'just incr. dest row Else 'different record:new sheet If C.Row > 2 Then addTab shareSheet Set shareSheet = dealBook.Worksheets.Add shareSheet.Name = Left(C.Value, 30) Debug.Print Left(C.Value, 30) title_range.Copy shareSheet.[a1] 'copy titles rCount = 2 End If sRange.Copy shareSheet.Cells(rCount, 1) Next C addTab shareSheet AlphaSheet Application.ScreenUpdating = False End Sub Sub addTab(shareSheet As Worksheet) shareSheet.ListObjects.Add(xlSrcRange, shareSheet.UsedRange, , xlYes).Name _ = "Table" & shareSheet.Name [a1].Select End Sub Sub bordallTHIN(fenceMe As Range) '24 4 13 'Dim indb(4) As XlBordersIndex 'NB std.Order ' indb = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal) 'indb(1) = xlEdgeLeft: indb(2) = xlEdgeTop 'indb(3) = xlEdgeBottom: indb(4) = xlEdgeRight For bind = 0 To 5 With fenceMe.Borders(indb(bind)) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 0 .TintAndShade = 0 End With Next End Sub Sub ACSMergeDmerge() 'Print Format(Now(), "dddd dd mmmm yyyy") 'Wednesday 24 April 2013 ' s|-|| t not working For Each C In Selection With C .Range(Cells(1, 1), Cells(1, 14)).UnMerge .Range(Cells(1, 1), Cells(1, 9)).Merge .Range(Cells(1, 10), Cells(1, 11)).Merge .Range(Cells(1, 13), Cells(1, 14)).Merge ' bordAllMed (.Cells(1, 1)) 'bordAllMed (.Range(.Cells(1, 1), .Cells(1, 9))) 'bordAllMed (.Cells(1, 10)) 'bordAllMed (.Cells(1, 12)) ' bordAllMed bordallTHIN .Range(Cells(1, 1), Cells(1, 14)) End With Next C ' Macro7 Macro wrote stiaght in ... ' Exit Sub End Sub Sub NLbasic2013() Dim nm As Name On Error Resume Next If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub Debug.Print "====================================================================================================" Set nms = ActiveWorkbook.Names Set wks = Worksheets(1) For r = 1 To nms.Count Debug.Print r & ")"; Debug.Print nms(r).Name & " "; Debug.Print nms(r).RefersToRange.Address & " "; Debug.Print nms(r).Item(1).Parent.Name & " "; 'pick out first cell Debug.Print Next End Sub Sub setACStext() '''''''''''''''''''''Thursday 25 April 2013 On Error Resume Next If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub txn = ActiveSheet.[txn].Value For i = LBound(txn, 1) To UBound(txn, 1) Debug.Print txn(i, 1); " "; txn(i, 2) ActiveWorkbook.Names(txn(i, 1)).Delete ActiveWorkbook.Names.Add Name:=txn(i, 1), RefersTo:="=" & txn(i, 2) Next i End Sub Sub setACStextALL() 'Range("Ext_text,CustA_text,BOH_text,Cool_text,Vert_text,Security_text,HandS_text").Select Range("Ext_chk_bnd,CustA_chk_bnd,BOH_chk_bnd,Cool_chk_bnd,Vert_chk_bnd,Security_chk_bnd,HandS_chk_bnd").Select 'Range("Ext_bcomm,CustA_bcomm,BOH_bcomm,Cool_bcomm,Vert_bcomm,Security_bcomm,HandS_bcomm").Select End Sub Sub ACStabupdater() '' projects acc db' ' end 08/03/2013 16:11:00 'Sheets("Meta data Breakdown").Select With [txn] ''' .Value = [h4:h200].Value '.Format = [h4:h200].Format method d work ''' .Item(1).Value = "fieldname_access" .Replace What:="_text", Replacement:="_bcomm", LookAt:=xlPart ' .Replace What:="_text", Replacement:="_chk_bnd", LookAt:=xlPart .Replace What:="$q$", Replacement:="$p$", LookAt:=xlPart .Replace What:="$r$", Replacement:="$p$", LookAt:=xlPart End With MsgBox "end", , Now ' End Sub Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaType_SlowSK4_ISS_PD() Dim ch As String ''BETTER labels are self generating ... ' as usual SK progs dont like sub routine why ?? labADDSwitch = "%jdcl" 'access comand key string (LAME) tssep = "{ENTER}" ' "{TAB}" ' apSwitch = "%{TAB}" SendKeys apSwitch 'switch applications... Breather 1 presscount = 1 For Each C In Selection For Cc = 1 To Len(labADDSwitch) 'type comands first ... ch = Mid(labADDSwitch, Cc, 1) SendKeys ch, True For j = 1 To 3: gasp: Next j Next Cc SendKeys tssep SendKeys tssep For P = 1 To 5 * presscount gasp SendKeys "{DOWN}" 'moves text a bit to avoid overtype Next P For Cc = 1 To Len(C.Value) 'dice+ add label text... ch = Mid(C, Cc, 1) SendKeys ch, True gasp Next Cc SendKeys tssep presscount = presscount + 1 Next C End Sub Sub CalCWconditionals() '================================================================================ '================================================================================ 'Wednesday 08 May 2013 ''Code by Chris N Wright MSc. '================================================================================ '================================================================================ ' ##### # # ' # # # # ##### # #### # # # ##### # #### # # ##### ' # # # # # # # # # # # # # # # # # # ' # ###### # # # #### # # # # # # # ###### # ' # # # ##### # # # # # ##### # # ### # # # ' # # # # # # # # # # # # # # # # # # # # ' ##### # # # # # #### ## ## # # # #### # # # '====useful for general Cond formatting (?)====================================== '================================================================================ Dim fc As FormatCondition Windows("CW calendar.xlsx").Activate Range("a4").Activate Range("B4").Activate Range("B4:H4,B6:H6,B8:H8,B10:H10,B12:H12,B14:H14").Select For Each fc In Selection.FormatConditions fc.Delete Next Set fc = Selection.FormatConditions.Add(Type:=xlExpression, Formula1:="=WEEKDAY(B4)=1") 'Sunday fc.SetFirstPriority With fc.Font .Color = -16777024 'red .TintAndShade = 0 End With fc.StopIfTrue = False Set fc = Selection.FormatConditions.Add(Type:=xlExpression, Formula1:="=WEEKDAY(B4)=7") 'saturday fc.SetFirstPriority With fc.Font .Bold = True .Color = -16777024 'red .TintAndShade = 0 End With fc.StopIfTrue = False Set fc = Selection.FormatConditions.Add _ (Type:=xlExpression, Formula1:="=not(ISERROR(MATCH(B4,BH,0)))") ' Bank holiday ... fc.SetFirstPriority With fc.Font .Bold = True .Color = -10477568 'blue .TintAndShade = 0 End With fc.StopIfTrue = False End Sub Sub jj() ActiveWindow.Zoom = 85 Rows("1:1").Insert Shift:=xlDown [A1:I1].Value = Array("FileBatchNumber", "DownloadName", "MessageExtract", "Subject", _ "DateReceived", "FileAttachName", "Sender", "Type", "Status") End Sub Sub ConcMSlive() [g1] = collNEW(Selection, ";", "") End Sub Sub list_50ISS() 'menu texts ''Sheets.Add ''i = 1 Set currentSheets = Application.ActiveWorkbook.Worksheets For Each ThisSheet In currentSheets dp ThisSheet.Name 'ThisSheet.Select ' ActiveWindow.Zoom = 50 '' With Range("b" & i) '' .value = ThisSheet.Name '' .Font.Size = 14 'For Each h In ThisSheet.Shapes ' h.Delete 'Next h '' '' .EntireColumn.AutoFit '' End With ''i = i + 1 '' ''''' killgrid '''' ActiveWindow.DisplayGridlines = False '''' Range("a1").Select Next ThisSheet End Sub Sub d1spannerAgainISSversion() Dim buf As Variant Dim i As Long 'Get values as an array buf = Selection.Value 'loop through the array and add a string to each element For i = LBound(buf, 1) To UBound(buf, 1) buf(i, 1) = Application.WorksheetFunction.Trim(buf(i, 1)) buf(i, 1) = Application.WorksheetFunction.Proper(buf(i, 1)) 'collect more here Next 'Put the array back into the worksheet Selection.Value = buf End Sub Sub SusanHeaders_problem() ''================================================================================ '================================================================================ 'Tuesday 04 June 2013 ' Code by Chris N Wright MSc. '================================================================================ '================================================================================ ' ##### # # ' # # # # ##### # #### # # # ##### # #### # # ##### ' # # # # # # # # # # # # # # # # # # ' # ###### # # # #### # # # # # # # ###### # ' # # # ##### # # # # # ##### # # ### # # # ' # # # # # # # # # # # # # # # # # # # # ' ##### # # # # # #### ## ## # # # #### # # # Dim HSP_wkbk As Workbook Dim HSP_sht As Worksheet Dim HSP_path As String ''Dim HSP1_range As Range ''Set HSP_range = xxxxxxx ''Set HSP_range = xxxxxxx HSP_path = "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\Escalation formattingSP1" ChDir HSP_path Set HSP_wkbk = Workbooks.Open(Filename:="tempHF.xlsx") '''' Sheets("1 Churchill Place").Select '''' ActiveWindow.View = xlPageBreakPreview '''' ActiveWindow.SmallScroll Down:=-18 Sheets(1).Activate Range("a2").Select For Each HSP_sht In HSP_wkbk.Sheets i = i + 1 If HSP_sht.Visible = xlSheetVisible Then HSP_sht.Select Debug.Print i; " "; HSP_sht.Name ActiveWindow.View = xlPageLayoutView ActiveWindow.Zoom = 100 'run worker prog killSplits 'remove any panel spilits topRow ActiveSheet 'header margin SPpenvelope ' addinh pngs ... landSlimMarg ActiveSheet 'landscape/margins botRow ActiveSheet End If Next Breather 4 HSP_wkbk.Close True End Sub Sub HFpix__IN(hfSheet As Worksheet) ' 04/06/2013 14:01:49 SusanHeaders_problem With hfSheet.PageSetup Application.PrintCommunication = False .LeftHeaderPicture.Filename = _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\Escalation formattingSP1\BARChead.png" .LeftFooterPicture.Filename = _ "\\issukacpfil001\OffLineFiles$\chwright\Desktop\workISS\Escalation formattingSP1\ISSfoot.png" With .LeftHeaderPicture .Height = 23.25 .Width = 136.5 End With .LeftHeader = "&G" Application.PrintCommunication = False With .LeftFooterPicture .Height = 40 .Width = 52 End With .LeftFooter = "&G" Application.PrintCommunication = True End With End Sub Sub HFpixOUT(hfSheet As Worksheet) 'topRow Application.PrintCommunication = False '' deleting header PIC org. SusanHeaders_problem With hfSheet.PageSetup .LeftHeader = "" .LeftFooter = "" End With Application.PrintCommunication = True End Sub Sub killSplits() ActiveWindow.SplitRow = 0 ActiveWindow.SplitColumn = 0 End Sub Sub SPpenvelope() 'run from here test and correct ... 'semi auto ''================================================================================ '================================================================================ 'Tuesday 04 June 2013 ' Code by Chris N Wright MSc. '================================================================================ '================================================================================ ' ##### # # ' # # # # ##### # #### # # # ##### # #### # # ##### ' # # # # # # # # # # # # # # # # # # ' # ###### # # # #### # # # # # # # ###### # ' # # # ##### # # # # # ##### # # ### # # # ' # # # # # # # # # # # # # # # # # # # # ' ##### # # # # # #### ## ## # # # #### # # # Dim HSP_wkbk As Workbook Dim HSP_sht As Worksheet Set HSP_wkbk = ActiveWorkbook Set HSP_sht = ActiveSheet With HSP_sht .Cells(1, 1).Select HFpixOUT ActiveSheet HFpix__IN ActiveSheet '.Next.Select End With End Sub Sub landSlimMarg(hfSheet As Worksheet) Application.PrintCommunication = False With hfSheet.PageSetup .LeftMargin = Application.InchesToPoints(1) .RightMargin = Application.InchesToPoints(1) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .Orientation = xlLandscape End With Application.PrintCommunication = True End Sub Sub topRow(hfSheet As Worksheet) 'topRow Application.PrintCommunication = False '' deleting header PIC org. SusanHeaders_problem With hfSheet.PageSetup .TopMargin = Application.CentimetersToPoints(1.6) End With Rows("1:1").RowHeight = 20.25 Application.PrintCommunication = True End Sub Sub botRow(hfSheet As Worksheet) 'topRow Application.PrintCommunication = False '' deleting header PIC org. SusanHeaders_problem With hfSheet.PageSetup .BottomMargin = Application.CentimetersToPoints(2.25) End With ActiveWindow.DisplayGridlines = False Application.PrintCommunication = True End Sub Sub FOcalendarsheets1() 'Sub Macro11() ' ' Macro11 Macro 10/06/2013 10:29:32 calenadr again '' Windows("CW calendar.xlsx").Activate ''loop heer For i = 1 To 12 Sheets("template").Copy After:=Sheets(ActiveWorkbook.Sheets.Count) Range("L1").FormulaR1C1 = i ActiveSheet.Calculate ActiveSheet.Name = Range("M1").Value Next ''"end" End Sub Sub listmodules1VBE() 'after Pearson http://www.cpearson.com/excel/vbe.aspx Dim VBAEditor As VBIDE.VBE Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBAEditor = Application.VBE ''''''''''''''''''''''''''''''''''''''''''' Set VBProj = VBAEditor.ActiveVBProject ''Set CodeMod = VBComp.CodeModule For Each VBComp In VBProj.VBComponents Debug.Print "======"; VBProj.Name; " "; VBComp.Name Set CodeMod = VBComp.CodeModule '' With CodeMod '' LineNum = .CountOfDeclarationLines + 1 '' Do Until LineNum >= .CountOfLines '' ProcName = .ProcOfLine(LineNum, ProcKind) '' rng.Value = ProcName '' rng(1, 2).Value = ProcKindString(ProcKind) '' LineNum = .ProcStartLine(ProcName, ProcKind) + _ '' .ProcCountLines(ProcName, ProcKind) + 1 '' Set rng = rng(2, 1) '' Loop '' End With Next End Sub Sub AddModuleToProject() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule) VBComp.Name = "Module1000" End Sub Sub DeleteModule() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("Module1000") VBProj.VBComponents.Remove VBComp End Sub Sub DeleteModule1210() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent On Error Resume Next Set VBProj = ActiveWorkbook.VBProject For i = 1 To 20 Set VBComp = VBProj.VBComponents("Module" & i) VBProj.VBComponents.Remove VBComp Next i End Sub '====================================================================== Sub collage__SwitchData() 'Const CORNER As String = "Type of Call received" Dim FfilelistRange As Range, fc As Range, utcornerRange As Range Dim colSh As Worksheet, s As Worksheet Dim PastePos As Integer ulog "SwD start" SDeleter ''Dim ShiftRow_int As Integer '= 9 'Range("Q9").= "shift row" ''Dim TypeCol_int As Integer ' = 1 'Range("A47").Selection.FormulaR1C1 = "tyeps of call" Set FfilelistRange = ThisWorkbook.Worksheets("SWITCH").Range("A2:A7") Set colSh = ThisWorkbook.Sheets.Add PastePos = 1 ''''''''''''colSh.Cells.Clear ''' End With PastePos = PastePos + 1 'top loop For Each fc In FfilelistRange PastePos = PastePos + 1 colSh.Cells(PastePos, 1).Value = ">>>>>>>>>>>>>>>>>>>" & fc.Value PastePos = PastePos + 1 Set ClogBook = Workbooks.Open(fc.Value, 0, True) ulog fc.Offset(0, 1).Value ActiveSheet.UsedRange.Copy colSh.Cells(PastePos, 1) PastePos = realRows(colSh) PastePos = PastePos + 1 ClogBook.Close False Next fc ThisWorkbook.Save ulog "SWD finish" End Sub Sub ExtractALLREX() ''based on columns only Dim REX As Range, Hrex As Range Dim searchTerm As Variant, Fir As Range, off_L As Integer, off_R As Integer Set Fir = [i:i] searchTerm = "hatton" off_R = 16 off_L = -7 With [i1] 'h row and select colun always! Set Hrex = Range(.Offset(0, off_L), .Offset(0, off_R)) End With Set REX = Union(Hrex, findALLREX(searchTerm, Fir, off_L, off_R)) REX.Copy 'array method? Sheets.Add ActiveSheet.Paste ActiveSheet.Name = searchTerm End Sub Sub Lyre3_poor() 'very lame 26/06/2013 10:33:04 ' quick and v dirty ' I want to clear all lyre3 cells ... For i = 20 To 800 Range("a" & i).Select If ActiveCell.Value Like "*Lyre3*" Then ActiveCell.ClearContents Next End Sub Sub Lyre3_good() 'very lame 26/06/2013 10:33:04 ' MUCH quicker exec and good Dim buf As Variant buf = Range("a20:a800").Value For i = LBound(buf, 1) To UBound(buf, 1) 'this is memory hungry though If buf(i, 1) Like "*Lyre3*" Then buf(i, 1) = "" Next Range("a20:a800").Value = buf End Sub Sub Text2Rows() '======================================================================================================================= ' 02/07/2013 11:15:12 'Code by Chris N Wright MSc. '=========================================================================================================================== ' ___ _ _ ___ _ ' / __| __ _| | |_ _ / __| |_ _____ _____ _ _ ___ ' \__ \/ _` | | | || | \__ \ _/ -_) V / -_) ' \(_-< ' |___/\__,_|_|_|\_, | |___/\__\___|\_/\___|_||_/__/ ' |__/ Dim text_cell As Range Dim cellind_int As Integer, arrayCount As Integer Dim text_Buf As Variant Const SEP As String = "." ' text parse on sep and fill cells starting with active Set text_cell = ActiveCell text_Buf = Split(text_cell.Value, SEP) cellind_int = 0 For arrayCount = LBound(text_Buf, 1) To UBound(text_Buf, 1) If Len(text_Buf(arrayCount)) > 0 Then ' no nulls in If IsNumeric(text_Buf(arrayCount)) Then 'afflicted with sentence index nums text_Buf(arrayCount) = "" 'clear them out ... Else ' process out the chr10s text_Buf(arrayCount) = swc_t2r(text_Buf(arrayCount) & "") ' SS much neater with subroutine End If End If ' no nulls in Next arrayCount For arrayCount = LBound(text_Buf, 1) To UBound(text_Buf, 1) If Len(text_Buf(arrayCount)) > 0 Then text_cell.Offset(cellind_int, 0) = text_Buf(arrayCount) & SEP cellind_int = cellind_int + 1 End If Next arrayCount End Sub Public Function swc_t2r(instring As String) As String swc_t2r = "" Dim d As Variant, bc, ac bc = Array(Chr(10), Chr(9)) 'soft ret and tabs out ac = Array(" ", " ") 'spaces in... d = splitChars(instring) For i = LBound(d) To UBound(d) If Len(d(i)) > 0 Then For j = LBound(bc) To UBound(bc) If d(i) = bc(j) Then d(i) = ac(j) Next j End If Next i swc_t2r = joinChars(d) End Function Sub Marquee_erSatz() ' ' Macro1 Macro '01/07/2013 14:41 Workbooks.Add Application.WindowState = xlMaximized Application.DisplayFullScreen = True ActiveWindow.DisplayHeadings = False Application.DisplayFormulaBar = False ActiveWindow.DisplayGridlines = False Range("A17").Select ActiveWindow.Zoom = 265 ActiveWindow.SmallScroll Down:=6 For colM = 1 To 600 Select Case colM Mod 6 Case 1 txt = "Chris" Case 2 txt = "WILL" Case 3 txt = "be" Case 4 txt = "back" Case 5 txt = "Soon" Case 0 txt = "" & colM / 6 Case Else MsgBox "massive error" Exit Sub End Select Set cl = Cells(17, colM) cl.Value = txt If colM > 9 Then ActiveWindow.ScrollColumn = colM - 8 'this is the marquee line Breather 1 Next '' Exit Sub ActiveWindow.DisplayGridlines = True ActiveWindow.DisplayHeadings = True Application.DisplayFormulaBar = True Application.DisplayFullScreen = False ActiveWindow.Zoom = 100 Range("A1").Select ActiveWindow.Close False End Sub Sub actest() 'select one way and scroll to another Sheets.Add [a1].Select ActiveWindow.ScrollColumn = 10000 ActiveWindow.ScrollRow = 10000 End Sub Sub move_importdata123() 'methodical (crap)extraction from the Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range, d As Range, C As Range, i As Long Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Dim allad As String i = 1 Set projSheet = projBook.Sheets("ISS Projects & MAC Live Tracker") Set outSheet = projBook.Sheets("DataDump") outSheet.Activate [a1].Select Cells.Clear allad = "import_range_all" For Each C In projSheet.Range(allad) Set d = outSheet.Range(C.Address) C.Copy Destination:=d d.Value = C.Value 'i = i + 1 'If i = 100 Then Exit For Next C Application.ScreenUpdating = True ulog "finished" & i End Sub Sub pasting__tests5() '' Macro5 Macro '' "7/12/2013" ' Windows("correct Master Projects- Weekly Tracker working new 02 07 2013.xlsm"). _ Activate Range("f1").Copy Sheets("datadump").Select Range("A1").Select ActiveSheet.Paste Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("A3").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<<<<<<<<<<<<<<<<<<<'''''''''''''''''''''' Range("a5").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("a6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True '"end of pasting tests" End Sub Sub move_importdataV2() 'methodical (crap)extraction from the Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range, d As Range, C As Range, i As Long Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Dim allad As String i = 1 Set projSheet = projBook.Sheets("ISS Projects & MAC Live Tracker") Set outSheet = projBook.Sheets("DataDump") outSheet.Activate [a1].Select Cells.Clear allad = "a6:n50" allad = "import_all" projSheet.Range(allad).Copy outSheet.Range("A1").PasteSpecial Paste:=xlValues outSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats Application.ScreenUpdating = True ulog "finished importprep" End Sub Sub AccessFriendlyNames() '' projects acc db' ' end 08/03/2013 16:11:00 ''''''''''' _ 12/07/2013 16:21:57 With Selection '.Value = [h4:h200].Value '.Format = [h4:h200].Format method d work '.Item(1).Value = "fieldname_access" .Replace What:="%", Replacement:="PCT", LookAt:=xlPart .Replace What:=" ", Replacement:="_", LookAt:=xlPart .Replace What:="\", Replacement:="_", LookAt:=xlPart .Replace What:="/", Replacement:="_", LookAt:=xlPart .Replace What:=")", Replacement:="_", LookAt:=xlPart .Replace What:="(", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart .Replace What:="__", Replacement:="_", LookAt:=xlPart End With Range("Q1").FormulaR1C1 = "end" & Now Range("P4").Select ' End Sub Function peode(numstr As Long) Dim Nchain As Variant, i As Integer, movidig As String Application.Volatile peode = 0 Nchain = splitChars("" & numstr) movidig = Nchain(LBound(Nchain)) For i = LBound(Nchain) To UBound(Nchain) - 1 Nchain(i) = Nchain(i + 1) Next i Nchain(UBound(Nchain)) = movidig peode = 1 * joinChars(Nchain) End Function Sub inspextionNdx() 'sally Dim Ubarlfs As Variant, Bregion As Range, b As Range Dim insIndex As Integer Const COLOFF As Integer = 7 Sheets("inspection data").Select Set Bregion = Intersect([D6].CurrentRegion, [D6].EntireColumn) Ubarlfs = Bregion.Value Ubarlfs = Shavearray(Ubarlfs) Ubarlfs = L_U_V_A(Ubarlfs) 'go through unique values For i = LBound(Ubarlfs) To UBound(Ubarlfs) insIndex = 0 Debug.Print Ubarlfs(i) For Each b In Bregion If b.Value = Ubarlfs(i) Then If b.Value <> b.Offset(-1, 0).Value Then insIndex = insIndex + 1 'is this first record of a batch b.Offset(0, COLOFF).Value = "Inspection " & insIndex End If Next b Next i End Sub Sub lamestRECIP() SDeleter Sheets.Add Dim counTer As Long start: counTer = counTer + 1 [E5] = [E5] + (1 / counTer) If Right("" & counTer, 3) = "000" Then [d5] = counTer Range("h" & counTer / 1000).Value = [d5] Range("i" & counTer / 1000).Value = [E5] End If If counTer > 500000 Then Exit Sub GoTo start End Sub Sub Allroots() Dim rootSheet As Worksheet Dim Operand As Integer, Nthpow As Integer, i As Integer, iter As Integer Dim xGuess As Double, xGuessNew As Double Dim xGuessN As Double, xGuessN1 As Double Operand = 77 Nthpow = 5 xGuess = 2 Set rootSheet = Sheets.Add With rootSheet .Cells(1, 6) = Nthpow & " root of " & Operand For iter = 1 To 10 .Cells(iter, 1) = iter .Cells(iter, 2) = xGuess xGuessN = 1 For i = 1 To Nthpow xGuessN = xGuessN * xGuess Next xGuessN1 = xGuessN / xGuess xGuessNew = 1 xGuessNew = ((Nthpow - 1) * xGuessN + Operand) / (Nthpow * xGuessN1) dp xGuessNew xGuess = xGuessNew Next iter .Name = Nthpow & " root of " & Operand End With End Sub Sub PROTOchopdate() Dim cds As Variant, colls As String, lastdate As Date On Error Resume Next cds = splitChars(ActiveCell.Value) colls = "" For i = LBound(cds, 1) To UBound(cds, 1) colls = colls & cds(i) Debug.Print colls; CVDate(colls) lastdate = CVDate(colls) Next i Debug.Print Debug.Print lastdate ActiveCell.Offset(0, -1).Value = lastdate End Sub Sub slowcopyVL() 'dont usethis 2/9/13 Exit Sub 'had a lot ot prolems with excel memeory so took lame way out For iCol = 1 To 170 ulog Sheets("datadump cancelled").Cells(1, iCol).Value & ":" & iCol For irow = 1 To 300 Sheets("datadump").Cells(irow + 1300, iCol).Value = Sheets("datadump cancelled").Cells(irow, iCol).Value Next irow Breather 1 Next iCol ActiveWorkbook.Save End Sub Function chopdate(hassomedate) As Date ' Date at front Application.Volatile chopdate = 0 Dim cds As Variant, colls As String, lastdate As Date On Error Resume Next cds = splitChars("" & hassomedate) colls = "" For i = LBound(cds, 1) To UBound(cds, 1) colls = colls & cds(i) lastdate = CVDate(colls) Next i chopdate = lastdate End Function Sub testchopdate() On Error Resume Next For Each C In [A2:A359] Debug.Print C.Row C.Offset(0, 2).Value = chopdate(C.Offset(0, 3).Value) Next C End Sub Sub staffListindCHAOS() 'trying pull data together quickly ''06/08/2013 14:53:37 Dim sflistRange As Range, dataCorner As Range Dim sfBook As Workbook, sfSheet As Worksheet, sdataSheet As Worksheet Dim sfLen As Integer ThisWorkbook.Activate Sheets("Staff Files").Select Set sflistRange = [a55:a80] SDeleter sfLen = 1 Set sfSheet = Sheets.Add For Each C In sflistRange 'c.Select Debug.Print C.Value Set sfBook = Workbooks.Open(C.Value) For Each sdataSheet In sfBook.Worksheets If Application.WorksheetFunction.CountA(sdataSheet.Cells) > 0 Then sdataSheet.Select Next sdataSheet Set dataCorner = [a1] Finddata: If Len(dataCorner.Value) = 0 Then Set dataCorner = dataCorner.Offset(1, 0) GoTo Finddata: End If sfSheet.Cells(sfLen, 1) = C.Value sfLen = sfLen + 1 'dataCorner.EntireRow.Copy sfSheet.Cells(sfLen, 1) 'dataCorner.EntireRow.Offset(1, 0).Copy sfSheet.Cells(sfLen + 1, 1) dataCorner.CurrentRegion.Copy sfSheet.Cells(sfLen, 1) Breather 2 sfBook.Close False Set sfBook = Nothing sfLen = realRows(sfSheet) + 3 Next C End Sub Sub SuperFreq() '================================================================================ 'Wednesday 07 August 2013 'at 16:37:31 Code by Chris N Wright MSc. '================================================================================ Dim dataRange As Range, outRange As Range Dim dataBuf As Variant, col1u, col1, col1freq, outBuf As Variant Dim outSheet As Worksheet Dim i As Integer, j As Integer SDeleter Set dataRange = [a1].CurrentRegion dataBuf = dataRange.Value Set outSheet = Sheets.Add outBuf = outSheet.Range(Cells(1, 1), Cells(UBound(dataBuf, 1), 2 * UBound(dataBuf, 2))).Value 'the 2* is to allow room for frquencies .. For j = 1 To UBound(dataBuf, 2) col1 = selCOLarray(dataBuf, j) 'j = 1 => just fisrt column col1u = L_U_V_A(col1) stitle = col1u(1) col1u(1) = String(30, Chr(31)) SortArray1 col1u col1u(1) = stitle col1freq = Freqs2013(col1, col1u) For i = LBound(col1u) To UBound(col1u) outBuf(i, 2 * j - 1) = col1u(i) 'load up the value outBuf(i, 2 * j) = col1freq(i) 'load up the freq Next i Next j outSheet.Range(Cells(1, 1), Cells(UBound(dataBuf, 1), 2 * UBound(dataBuf, 2))).Value = outBuf End Sub Sub bScrolly() Randomize Workbooks.Add rpos = 1 For i = 1 To 1000 rpos = Int(rpos + 50 * Rnd()) Range("a" & rpos).Value = "blah" Next i Breather 5 Application.ActiveWindow.Zoom = 300 scrolly ActiveWorkbook.Close False End Sub Sub trimDB() '================================================================================ 'Wednesday 07 August 2013 'at 16:37:31 Code by Chris N Wright MSc. '================================================================================ Dim dataRange As Range, dataBuf As Variant Dim i As Integer, j As Integer SDeleter Set dataRange = [a1].CurrentRegion dataBuf = dataRange.Value For j = LBound(dataBuf, 2) To UBound(dataBuf, 2) For i = LBound(dataBuf, 1) To UBound(dataBuf, 1) dataBuf(i, j) = Application.WorksheetFunction.Trim(dataBuf(i, j)) 'load up the value and clear spaces 'dataBuf(i, j) = trim(dataBuf(i, j)) Next i Next j dataRange.Value = dataBuf End Sub Sub multi_swap() '================================================================================ '================================================================================ 'Wednesday 14 August 2013 ' at 14:45:05 ' Code by Chris N Wright MSc. '================================================================================ '================================================================================ ' ##### # # ' # # # # ##### # #### # # # ##### # #### # # ##### ' # # # # # # # # # # # # # # # # # # ' # ###### # # # #### # # # # # # # ###### # ' # # # ##### # # # # # ##### # # ### # # # ' # # # # # # # # # # # # # # # # # # # # ' ##### # # # # # #### ## ## # # # #### # # # Dim MSwapsRange As Range Set MSwapsRange = ThisWorkbook.Sheets("recode").[MSPairs] For Each C In Intersect(MSwapsRange, ThisWorkbook.Sheets("recode").Range("c:c")) wt_str = C.Value: rep_str = C.Offset(0, 1).Value Debug.Print wt_str, rep_str Selection.Replace What:=wt_str, Replacement:=rep_str, LookAt:=xlWhole Next C Findfixer Range("h63").FormulaR1C1 = "end" & Now Range("i63").Select ' End Sub Sub inspextionNdxTWO() '''''''' ''''''''14/08/2013 10:20:40 '''''' '''''''''''14/08/2013 10:20:40 ''''' ''''''''''''14/08/2013 10:20:40 '' '''''''''''''''14/08/2013 10:20:40 ''''' ''''''''''''14/08/2013 10:20:40 '''''''''''''''''''''14/08/2013 10:20:40 'adj 26sep13 Dim Ubarlfs As Variant, Bregion As Range, b As Range Dim insIndex As Integer Const COLOFF As Integer = 7 Sheets("inspection data").Select Set Bregion = Intersect([D6].CurrentRegion, [D6].EntireColumn) Ubarlfs = Bregion.Value Ubarlfs = Shavearray(Ubarlfs) Ubarlfs = L_U_V_A(Ubarlfs) 'go through unique values For i = LBound(Ubarlfs) To UBound(Ubarlfs) insIndex = 0 Debug.Print Ubarlfs(i); For Each b In Bregion If b.Value = Ubarlfs(i) Then If b.Value <> b.Offset(-1, 0).Value Then insIndex = insIndex + 1 'is this first record of a batch b.Offset(0, COLOFF).Value = "Inspection " & insIndex b.Offset(0, COLOFF + 1).Value = Now End If Next b 'new Debug.Print "Inspection " & insIndex If insIndex > 1 Then For Each b In Bregion If b.Value = Ubarlfs(i) And b.Offset(0, COLOFF).Value <> "Inspection " & insIndex Then b.Offset(0, COLOFF + 2).Value = "superfluous Data ? " b.Offset(0, COLOFF + 1).Value = Now End If Next b End If Next i End Sub Sub getCallStats() '' Macro5 Macro '' weds 14 8 13 SDeleter Columns("B:B").SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Sheets.Add ActiveSheet.Paste Columns("A:A").Insert Shift:=xlToRight south = realRows(ActiveSheet) Cells(south, 2).Select temPdate = Cells(south, 2).Value For irow = south To 1 Step -1 If Cells(irow, 2).Value Like "*2013" Then temPdate = Cells(irow, 2).Value End If Cells(irow, 1).Value = temPdate Next irow [a1].Select End Sub Sub addingFA() ' ' Macro5 Macro158 '15/08/2013 10:58:26 Windows("SF.xlsx").Activate Range("B1063:B1124").FormulaArray = "=trim(RC[1]:R[61]C[1] & char(32) & RC[2]:R[61]C[2])" End Sub Sub name_magic() ' iassume firstname sirname in adj colmuns aboth sheets ... '================================================================================ '================================================================================ ' Thursday 15 August 2013 ' at 13:11:33 ' Code by Chris N Wright MSc. '================================================================================ '================================================================================ 'FROM magic matcher ... '''''' Macro5 Macro ''''' '''''''''workbooks '''''''' ActiveWindow.ActivateNext '''''''' ActiveWindow.ActivateNext '''' Set selectRange1 = Workbooks("SF.xlsx").sheets("fteDesks clean st2").Range("E2:E20") '' Set selectRange2 = Workbooks("Split Of Employees 130703.xlsx").sheets("Master").Columns ("C:C") Dim selectRange1 As Range Dim selectRange2 As Range Dim c1 As Range Dim c2 As Range Dim Mrows As String Dim Maddress As String str_prompt1 = "select name cells" str_prompt2 = "select LOOKUP for cells" 'no Set selectRange1 = Application.InputBox(prompt:=str_prompt1, Type:=8, Title:="name_magic") 'no Set selectRange2 = Application.InputBox(prompt:=str_prompt2, Type:=8, Title:="name_magic") ''Workbooks.Open _ ("\\issukacpfil001\OffLineFiles$\chwright\Desktop\StaffFiles Download\Split Of Employees 130703.xlsx") Workbooks.Open Filename:="\\issukacpfil001\OffLineFiles$\chwright\Desktop\StaffFiles Download\220819 - All Employees to Knowledge Centre.xlsx", _ Password:="payroll2013" Workbooks.Open ("\\issukacpfil001\OffLineFiles$\chwright\Desktop\StaffFiles Download\SF2.xlsx") Set selectRange1 = Workbooks("SF2.xlsx").Sheets("fteDesks clean st2").Range("E2:E1124") 'Set selectRange2 = Workbooks("Split Of Employees 130703.xlsx").Sheets("Master").Columns("C:C") Set selectRange2 = Workbooks("220819 - All Employees to Knowledge Centre.xlsx").Sheets("Master").Columns("i:i") selectRange1.Offset(0, -2).ClearContents For Each c1 In selectRange1 '------main loop through sirname------------------Start Application.StatusBar = "Percentage" & Format(c1.Row / selectRange1.Count, " 00.00%") Mrows = "": Maddress = "" Set c2 = Nothing Set c2 = selectRange2.Find(c1.Value, , , xlWhole) 'find first match If c2 Is Nothing Then '------ NOthing Debug.Print "not found"; c1.Value; " source row "; c1.Row c1.Offset(0, -2).Value = "Not Found" Else '------something found Debug.Print "Match FOUN6D "; c1.Value; " source row "; c1.Row Maddress = c2.Address 'compare firstnames Do Set c2 = selectRange2.FindNext(c2) If Trim(c1.Offset(0, -1).Value) = Trim(c2.Offset(0, -1).Value) Then '------firstname match 1 col to left Debug.Print " FOUND First-name "; Trim(c2.Offset(0, -1).Value); " FN row"; c2.Row Mrows = Mrows & ";" & c2.Row Else Debug.Print " not found firstname "; Trim(c2.Offset(0, -1).Value); "FN row"; c2.Row c1.Offset(0, -2).Value = "no firstname match" End If Loop While Not (c2 Is Nothing) And c2.Address <> Maddress If Len(Mrows) > 0 Then c1.Offset(0, -2).Value = Right(Mrows, Len(Mrows) - 1) ' 2 col to left for row data,remove leading comma End If ' Next c1 '------main loop through sirname------------------Finish ''Dim MyCheck ''MyCheck = "aBBBa" Like "a*a" ' Returns True. ''MyCheck = "F" Like "[A-Z]" ' Returns True. ''MyCheck = "F" Like "[!A-Z]" ' Returns False. ''MyCheck = "a2a" Like "a#a" ' Returns True. ''MyCheck = "aM5b" Like "a[L-P]#[!c-e]" ' Returns True. ''MyCheck = "BAT123khg" Like "B?T*" ' Returns True. ''MyCheck = "CAT123khg" Like "B?T*" ' Returns False. End Sub Sub trim_me() 'in the crap style ...22/08/2013 12:12:26 Set selectRange1 = Workbooks("SF2.xlsx").Sheets("fteDesks clean st2").Range("d2:E1124") For Each C In selectRange1 C.Select C.Value = Trim(C.Value) 'gasp Next C End Sub Sub DU_set_filterISS() '' 27/08/2013 13:19:27 Dim sirname As String ' Windows("220819 - All Employees to Knowledge CentreMOBIN_TWO.xlsx").Activate sirname = ActiveCell.Value Sheets("Comms List").Select '' Range("Table1[[#Headers],[ Last name]]").Select '' Application.CutCopyMode = False '' Selection.AutoFilter ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("Table1").Range.AutoFilter _ Field:=1, Criteria1:="=*" & sirname & "*" ''ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1 End Sub Sub vl_loop() 'very lame 'SF project lStart: ActiveCell.Offset(1, 0).Select If ActiveCell.Value = "o" Then ActiveCell.Value = "ok" i = i + 1 dp i End If GoTo lStart End Sub Sub excelgrandtour() '29 aug 13 Application.ScreenUpdating = False xllimit = 1048576 Workbooks.Add [a1].Select Do ActiveCell.Offset(1, 0).Select If ActiveCell.Row = xllimit Then ActiveCell.Value = Now dp Now & ":" & ActiveCell.Column Cells(1, ActiveCell.Column + 1).Select End If Loop Until ActiveCell.Column = 1000 End Sub Sub DU_set_filterISS2() '' 27/08/2013 13:19:27 Dim sirname As String Windows("220819 - All Employees to Knowledge Centre_THREE.xlsx").Activate sirname = ActiveCell.Value Sheets("FTE List").Select ActiveSheet.Range("$A$1:$N$1124").AutoFilter Field:=5 ActiveSheet.Range("$A$1:$N$1124").AutoFilter Field:=5, Criteria1:="=*" & sirname & "*" End Sub Sub move_staff_data() 'methodical (crap)extraction from the Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range, d As Range, C As Range, delusRange As Range Dim colDel As Integer Windows("220819 - All Employees to Knowledge Centre_FIVE.xlsx").Activate Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Sheets("Master List 5").Select Range("A1").Select Dim allad As String i = 1 Set projSheet = projBook.Sheets("Master List 4") Set outSheet = projBook.Sheets("Master List 5") outSheet.Activate [a1].Select Cells.Clear allad = "import_all" allad = "a1:ca3800" outSheet.Range(allad).Value = projSheet.Range(allad).Value Set delusRange = Columns(1000) 'doesntwork For colDel = 1 To 100 Cells(2, colDel).Value = colDel If Cells(1, colDel).Value = 1000 Then Set delusRange = Union(delusRange, Columns(colDel)) End If Next colDel delusRange.Delete Shift:=xlToLeft Application.ScreenUpdating = True ulog "finished transfer staff denormaln" & Now End Sub Sub DU_set_filterISS_00() Dim answerRange As Range Dim str_Prompt As String Dim sirname As String, FirstName As String, pon As String Windows("220819 - SIX__All Employees to Knowledge Centre.xlsx").Activate sirname = ActiveCell.Value FirstName = ActiveCell.Offset(0, 1).Value Sheets("Payroll List").Select ActiveSheet.Calculate ActiveSheet.Range("$A$1:$U$3711").AutoFilter Field:=4 ActiveSheet.Range("$A$1:$U$3711").AutoFilter Field:=4, Criteria1:="=*" & sirname & "*" Breather 1 str_Prompt = "select correct record" str_Prompt = str_Prompt & sirname & FirstName Set answerRange = Application.InputBox(prompt:=str_Prompt, Type:=8, Title:="DU_set_filterISS_00") If answerRange Is Nothing Then Set answerRange = [d3720] Set answerRange = Intersect(answerRange.Item(1).EntireRow, Columns(2)) pon = answerRange.Value If Len(pon) = 0 Then pon = "No match " & Format(Now, "hh-mm:dd/mm/yy") Sheets("Comms List").Select Cells(ActiveCell.Row, 5).Value = pon End Sub Sub qsetFilter() ActiveSheet.Range("$A$3:$Y$3765").AutoFilter Field:=4, Criteria1:="#N/A" ActiveSheet.Range("$A$3:$Y$3765").AutoFilter Field:=9, Criteria1:="<>Cleaning Operative" End Sub Sub move_importdataV3() 'methodical Betterextraction from the Dim projBook As Workbook Dim projSheet As Worksheet, outSheet As Worksheet Dim projRange As Range, projseOnlyRange As Range, d As Range, C As Range, i As Long Application.ScreenUpdating = False SDeleter Set projBook = ActiveWorkbook Dim allad As String i = 1 Set projSheet = projBook.Sheets("ISS Projects & MAC Live Tracker") Set outSheet = ThisWorkbook.Sheets.Add outSheet.Activate [a1].Select Cells.Clear allad = "import_all" allad = "a5:dj1380" projSheet.Range(allad).Copy outSheet.Range("A1").PasteSpecial Paste:=xlValues outSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats Application.ScreenUpdating = True ulog "finished importprep" End Sub Sub movecanx() 'transfer of cancelled records '================================================================================ '================================================================================ 'Tuesday 10 September 2013 'at 10:50:54 ' Code by Chris N Wright MSc. '================================================================================ Dim cxBuf As Variant Dim livenum As Integer cxBuf = Sheets("canx").Range("A1").CurrentRegion.Value livenum = realRows(Sheets("liveProj")) With Sheets("liveProj") ' remember to . the cells .Range(.Cells(livenum + 1, 1), .Cells(livenum + UBound(cxBuf, 1), UBound(cxBuf, 2)) _ ).Value = cxBuf End With End Sub Sub comments_proj4() If Range("a1").Value <> "ISS_Projects_URN" Then Exit Sub Set d = [f1] d.CurrentRegion.Clear For Each C In Columns(2).SpecialCells(xlCellTypeConstants, 23) clines = Split(C.Value, Chr(10)) For i = LBound(clines, 1) To UBound(clines, 1) Set d = d.Offset(1, 0) d.Select d.Value = clines(i) d.Offset(0, -1).Value = C.Offset(0, -1).Value Next i Next C End Sub Sub blue_flies() Randomize Const NUMSHOTS As Integer = 300 ' Macro5 Macro '' Workbooks.Add Cells.Select Selection.ColumnWidth = 1.57 Selection.RowHeight = 8.25 Selection.ColumnWidth = 0.92 ActiveWindow.Zoom = 70 For rEp = 1 To 6 Application.ScreenUpdating = False For ic = 1 To NUMSHOTS cr = 1 + Int(Rnd() * 150) Cc = 1 + Int(Rnd() * 100) With Cells(cr, Cc).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = -0.249977111117893 End With Next ic Application.ScreenUpdating = True 'Breather 1 Cells.Clear Next rEp 'Breather 5 ActiveWindow.Close False End Sub Sub wanderRight() Randomize Const NUMSHOTS As Integer = 500 ' Macro5 Macro '' Workbooks.Add Cells.Select '' Selection.ColumnWidth = 1.57 '' Selection.RowHeight = 8.25 '' Selection.ColumnWidth = 0.92 ActiveWindow.Zoom = 160 [a1].Select For ic = 1 To NUMSHOTS ActiveCell.Offset(cr, Cc).Select cr = 1 + Int(Rnd() * 5) Cc = 1 + Int(Rnd() * 3) Breather 1 Next ic ActiveWindow.Close False End Sub Sub Marquee_erSatz2() ''05/04/2017 16:12:10 Const MARQUEE As String = "Ich war anwesend, aber nicht wirklich da" Dim Mq As Variant, cl As Range, _ colM As Integer, rEp As Integer, ColPOS As Integer Workbooks.Add Application.WindowState = xlMaximized Application.DisplayFullScreen = True ActiveWindow.DisplayHeadings = False Application.DisplayFormulaBar = False ActiveWindow.DisplayGridlines = False Range("A17").Select ActiveWindow.Zoom = 256 ' ActiveWindow.Zoom = 25 ActiveWindow.SmallScroll Down:=6 ColPOS = 1 Mq = Split(MARQUEE, " ") For rEp = 1 To 5 For colM = LBound(Mq) To UBound(Mq) Set cl = Cells(17, ColPOS) cl.Value = Mq(colM) If ColPOS > 9 Then ActiveWindow.ScrollColumn = ColPOS - 8 '^^^^^this is the marquee line^^^^^^^^^^^^^^^^^^^^^^^^^^^ breather2 2 ColPOS = ColPOS + 1 Debug.Print colM; " "; ColPOS Next colM breather2 5 Next rEp ActiveWindow.DisplayGridlines = True ActiveWindow.DisplayHeadings = True Application.DisplayFormulaBar = True Application.DisplayFullScreen = False ActiveWindow.Zoom = 100 Range("A1").Select ActiveWindow.Close False End Sub '================================================================================ ' C E M E X u t i l i t i e s ''Code by Chris N Wright MSc. '================================================================================ '================================================================================ ' ##### # # ' # # # # ##### # #### # # # ##### # #### # # ##### ' # # # # # # # # # # # # # # # # # # ' # ###### # # # #### # # # # # # # ###### # ' # # # ##### # # # # # ##### # # ### # # # ' # # # # # # # # # # # # # # # # # # # # ' ##### # # # # # #### ## ## # # # #### # # # '================================================================================ '================================================================================ Sub launchHours() 'gets timesheet Workbooks.Open (ThisWorkbook.Sheets("kkontrol").[housLaunch].Value) End Sub Sub range2links() 'lazy lazy 5 6 18 For Each C In Selection C.Activate With ActiveCell ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=.Value _ , TextToDisplay:=.Value End With Next End Sub Sub cdt() Dim textline(1 To 19), cdtRange As Range Workbooks.Add ActiveSheet.Name = "Callahan" Set cdtRange = [a1:i19] textline(1) = "FirstName|LastName|CompanyName|Title|Address|City|StateOrProvince|PostalCode|WorkPhone" textline(2) = "Janée|Baugher|Baugher Biotech|Owner|220 Newbury St.|Boston|MA|2116|6175552212" textline(3) = "Bonnie|Christensen|Dance Academy|<>|4220 Main St.|Dallas|TX|75226|2145555541" textline(4) = "Darren|Demerais|L'École Française|Instructor|1020 Ranier Ave. S.|Seattle|WA|98144|2065557788" textline(5) = "Judy|Doyle|Paper Dolls Interiors|Owner|472 Lexington Ave.|New York|NY|10017|2125554220" textline(6) = "Kevin|Geloff|Ballard Academy|Principal|2312 N.W. 99th St.|Seattle|WA|98117|2065556068" textline(7) = "Gilbert|Greenman|Greenman, Delp & Whitney|Attorney|23 W. 48th St. #2|New York|NY|10020|2125554400" textline(8) = "Hardy|Griffin|Ballard Brewery|Master Brewer|115 Leary Wy.|Seattle|WA|98115|2065555317" textline(9) = "David|Hunting|Software Solutions|Consultant|39 E. 64th St.|New York|NY|10021|2125557880" textline(10) = "Rob|MacDougall|<>|<>|4550 W. Jefferson Blvd.|Los Angeles|CA|90016|2135558221" textline(11) = "Michael|Mahoney|Design Specialists|<>|3300 Colorado Blvd.|Denver|CO|80205|3035551108" textline(12) = "Kevin|O'Halloran|Editorial Consultants|Editor|300 Park Avenue #1301|New York|NY|<>|2125552342" textline(13) = "Maura|O'Keefe|Helping Hand|Director|31 E. Houston St. #6|New York|NY|10012|2125557154" textline(14) = "Steve|Saunders|<>|<>|3408 S.E. 11th Ave.|Portland|OR|97202|5035558322" textline(15) = "Kara|Stepanian|Big Bad Books|Publisher|480 Commonwealth Ave.|Boston|MA|2215|6175555452" textline(16) = "Deborah|Sundberg|Scissor's Edge|Owner|1005 Peachtree St. N.E.|Atlanta|GA|30309|4045556000" textline(17) = "Julie|Treece|<>|<>|835 E. Prospect|Seattle|WA|98102|2065556068" textline(18) = "Paul|Vomund|Snowboard Central|Sales Representative|22 Market St.|San Francisco|CA|94111|4155553244" textline(19) = "Charles|Willmes|<>|<>|27 Christopher St.|<>|NY|10014|2125552219" For ri = 1 To 19 Intersect(cdtRange, Rows(ri)).Value = Split(textline(ri), Chr(124)) Next ri cdtRange.Replace What:="<>", Replacement:="", LookAt:=xlPart ActiveSheet.ListObjects.Add(xlSrcRange, cdtRange, , xlYes).Name = "Table1" Range("A1").Select Range("A25").Value = Now() & " CNW" End Sub Sub SKfunAT_cemex() 'not finished 'Monday 30 July 2018 10:08 alt tab ok 'nullified screen saver 'Monday 18 June 2018 10:45 just about working 'Wed 29.06.16 10:20:39 'Dim skbook As Workbook 'Set skbook = Workbooks.Add [a1].Select Breather 2 apSwitch = "%{TAB}" Breather 2 SendKeys apSwitch 'switch applications.Manually not requeired at cemex to Textpad ... tssep = "{RIGHT}" '"{TAB}" '"{ENTER}" ' Breather 5 '60x(5+55) give the hour and no SS For i = 1 To 10 '' SendKeys "Back Soon=>>>>>" '' SendKeys tssep breather2 5 '' SendKeys Format(Now, "hh:mm:ss") SendKeys tssep breather2 5 Next i '' MORE SUBTIL End Sub Sub autologgingIN() 'Monday 18 June 20 'useful for daily report ? Dim anc As Range Set anc = ActiveCell anc.Hyperlinks(1).Follow 'IS there a link at this cell End Sub '====================================================================== Sub collage__SwitchDataCEMEX() 'Const CORNER As String = "Type of Call received" Dim FfilelistRange As Range, fc As Range, utcornerRange As Range Dim colSh As Worksheet, s As Worksheet Dim PastePos As Integer ulogXV "SwD start" SDeleter ''Dim ShiftRow_int As Integer '= 9 'Range("Q9").= "shift row" ''Dim TypeCol_int As Integer ' = 1 'Range("A47").Selection.FormulaR1C1 = "tyeps of call" Set FfilelistRange = ThisWorkbook.Worksheets("SWITCH").Range("A2:A6") Set colSh = ThisWorkbook.Sheets.Add PastePos = 1 ''''''''''''colSh.Cells.Clear ''' End With PastePos = PastePos + 1 'top loop For Each fc In FfilelistRange PastePos = PastePos + 1 colSh.Cells(PastePos, 1).Value = ">>>>>>>>>>>>>>>>>>>" & fc.Value PastePos = PastePos + 1 Set ClogBook = Workbooks.Open(fc.Value, 0, True) ulogXV fc.Offset(0, 1).Value ActiveSheet.UsedRange.Copy colSh.Cells(PastePos, 1) PastePos = realRows(colSh) PastePos = PastePos + 1 ClogBook.Close False Next fc ThisWorkbook.Save ulogXV "SWD finish" End Sub Sub addingSubtleHOURSHyperlink() Rem Tuesday 10 July 2018 :- 09:26 'automatically find todays cel ... Range("i1").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "today_cell", TextToDisplay:="select TODAY Hours here" ''name is ... today_cell =INDIRECT("$d$"&MATCH(hours!$L$1,hours!$D:$D,0)) End Sub Sub lotto2018lame() ' 11-7-18 ' lotto2018lame Macro ' ActiveSheet.Calculate Range("u5").Value = Range("u5").Value + 1 'draw counter If Range("H4").Value = 0 Then Range("T5").Value = Range("T5").Value + 1 'draw counter For Each C In [r7:r13] If C.Value = True Then C.Offset(0, 2).Value = C.Offset(0, 2).Value + 1 Next C End If End Sub Sub lameLoop() Workbooks("hours.xlsx").Activate Sheets("LT").Activate Range("A1").Select Range("R18").Value = Now Application.Calculation = xlCalculationManual Application.ScreenUpdating = False For rEp = 1 To 20000 'takes about a minute...slow lotto2018lame Next Range("R17").Value = Now Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub 'the array version of this needs 3 arrays and 4 variables and would be much faster Sub xlformulaSCtesting() Dim r As Range On Error Resume Next 'Set r = Selection.SpecialCells(xlCellTypeBlanks) Set r = Selection.SpecialCells(xlCellTypeFormulas) Err.Clear On Error GoTo 0 If r Is Nothing Then MsgBox "No formula cells" Else r.Select End If End Sub Sub NameRange_Add() 'PURPOSE: Various ways to create a Named Range 'SOURCE: www.TheSpreadsheetGuru.com chris something ? Dim cell As Range Dim Rng As Range Dim RangeName As String Dim CellName As String 'Single Cell Reference (Workbook Scope) RangeName = "Price" CellName = "D7" Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell 'Single Cell Reference (Worksheet Scope) RangeName = "Year" CellName = "A2" Set cell = Worksheets("Sheet1").Range(CellName) Worksheets("Sheet1").Names.Add Name:=RangeName, RefersTo:=cell 'Range of Cells Reference (Workbook Scope) RangeName = "myData" CellName = "F9:J18" Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell 'Secret Named Range (doesn't show up in Name Manager) RangeName = "Username" CellName = "L45" Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell, Visible:=False End Sub '================================================================================ 'Tuesday 31 July 2018 10:35 ''Code by Chris N Wright MSc. '================================================================================ '================================================================================ Sub rat() 'Rat RDA trends Dim ratBook As Workbook ''Dim ratBook1 As Workbook Dim ratSheet As Worksheet Dim ratKSheet As Worksheet Dim ratCSheet As Worksheet Dim ratRange As Range Dim ratKCell As Range Dim ratKontrolRange As Range Dim ratCOLLlen As Integer Dim ratInt1 As Integer Dim ratStr As String Dim ratStr1 As String Dim ratVar As Variant Dim ratVar1 As Variant Dim secAutomation As MsoAutomationSecurity 'store aut0matic security status secAutomation = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityForceDisable ThisWorkbook.Activate SDeleter Set ratKSheet = ThisWorkbook.Sheets("DStrawl2") Set ratCSheet = ThisWorkbook.Sheets("collectDS") Set ratKontrolRange = ratKSheet.[a2:a4] ''Workbooks.Open _ "Z:\Business Performance - National\Reports\REPORTS\Daily Sales\2016\01 - Jan\Daily Sales Report 04.01.16.xlsb", _ UpdateLinks:=False, ReadOnly:=True '''''''''''''''''''''main loop For Each ratKCell In ratKontrolRange Workbooks.Open ratKCell.Value, UpdateLinks:=False, ReadOnly:=True Set ratBook = ActiveWorkbook ''ratBook1 = something If SheetExists("Breakdown") Then Set ratSheet = ratBook.Sheets("Breakdown") ratCOLLlen = realRows(ratCSheet) + 1 '' ratSheet.[a1:j18].Copy ratCSheet.Range("a" & ratCOLLlen) '' ratSheet.[b2].Copy ratKCell.Offset(0, 5) ElseIf SheetExists("Snapshot") Then Set ratSheet = ratBook.Sheets("Snapshot") ratCOLLlen = realRows(ratCSheet) + 1 ratSheet.[a1:j18].Copy ratCSheet.Range("a" & ratCOLLlen) ratSheet.[b2].Copy ratKCell.Offset(0, 5) Else MsgBox "RAT cannot find either sheet", vbOKOnly, Now Debug.Print "RAT cannot find either sheet "; Now; ratBook.FullName End If ''ratRange = ratSheet.something ''ratRange1 = ratSheet1.something ratBook.Close False 'ratBook1.Close Set ratBook = Nothing Next ratKCell ''''''''''''''''''''''''''''''''' 'Set ratBook1 = Nothing 'Set ratSheet = Nothing 'Set ratSheet1 = Nothing 'Set ratRange = Nothing 'Set ratRange1 = Nothing 'Set ratRange2 = Nothing Application.AutomationSecurity = secAutomation ThisWorkbook.Save End Sub ' >>> rat ''codebuilder 2018 Sub upLABEL_CODE1() Rem trying to improve commetns and dating of code .. Debug.Print q = Chr(39) & String(80, "=") dp q dp q dp Format(Now(), "'dddd dd mmmm yyyy") dp Format(Now(), "'at hh:mm:ss") dp Chr(39) & String(52, " ") & "Code by Chris N Wright MSc." dp q dp q Debug.Print 'do some Dims and set=s in the same way 'dim xlApp as application For Each C In ThisWorkbook.Sheets("CodeBuilder3").[M78:M84] dp C.Value Next C For Each C In ThisWorkbook.Sheets("CodeBuilder3").[l97:l122] dp C.Value Next C End Sub ''Function dp(print_Me) ''dp = 0 '' ''Debug.Print print_Me '' '' ''End Function Sub upLABEL_CODE2() Rem trying to improve commetns and dating of code .. subName_str = InputBox("Input subroutine/name", "labelcode2", "Temp1auto") q = "sub " & subName_str & " " & Chr(39) & String(10, "-") dp q Debug.Print q = Chr(39) & String(80, "=") dp q dp q dp Format(Now(), "'dddd dd mmmm yyyy") dp Format(Now(), "'at hh:mm:ss") dp Chr(39) & String(52, " ") & "Code by Chris N Wright MSc." dp q dp q Debug.Print 'do some Dims and set=s in the same way 'dim xlApp as application For Each C In ThisWorkbook.Sheets("CodeBuilder4").[M78:M84] dp C.Value Next C For Each C In ThisWorkbook.Sheets("CodeBuilder4").[l97:l122] dp C.Value Next C q = "end sub " & Chr(39) & String(10, "-") & subName_str & " " dp q End Sub ''Function dp(print_Me) ''dp = 0 '' ''Debug.Print print_Me '' '' ''End Function Sub LABEL_CODE2018() Rem trying to improve commetns and dating of code .. subName_str = InputBox("Input subroutine/name", "labelcode2", "tmp") 'q = "sub " & subName_str & " " & Chr(39) & String(10, "-") ThisWorkbook.Sheets("CodeBuilder4").[i1].Value = subName_str 'dp q ThisWorkbook.Sheets("CodeBuilder4").Calculate For Each C In ThisWorkbook.Sheets("CodeBuilder4").[f4:f65] dp C.Value Next C ''For Each c In ThisWorkbook.Sheets("CodeBuilder4").[f22:f40] '[f4:f20] '' '' dp c.Value '' ''Next c End Sub