Because there are five sections to my macro which do one thing I'm not sure
if it's one part or something in the beginning I mean i could do a copy paste.
.. this is my first macro I have written, they are in order.
Sub TMSpart1()
'
' TMSpart1 Macro
' Macro written 10/26/2006 by MartÃn Hinojosa
'
'
Range("A1:A2").Select
Selection.ClearContents
Range("A3").Select
Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 1), Array(20, 1), Array(44, 1), Array(67, 1)
, Array(77, 1), _
Array(89, 1), Array(99, 1), Array(105, 1), Array(114, 1), Array(122,
1), Array(130, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Sub FindPayRoll()
'Macro Written 11-Oct-2006 by MartÃn Hinojosa
Dim r As Range 'Range Variable
Dim hrs As Range 'Hours
Dim nmList As Range 'Name List
Dim nm As Range 'Name
Dim ws1 As Worksheet 'Worksheet1 Variable
Dim ws2 As Worksheet 'Worksheet2 Variable
Dim nRow As Long 'Next Row
Set ws1 = Worksheets("TMSDL") 'Set Worksheet1 to Sheet with data to copy
from
Set ws2 = Worksheets("DATA") 'Set Worksheet1 to Sheet withdata to copy to
'Set NameList to a Named Range on Sheet3 contain all names that you want to
search
'for. an absent name will not be a problem as the program will just move on
Set nmList = Sheets("NAME").Range("NameList")
For Each nm In nmList.Cells 'Start looping through names
Set r = ws1.Cells.Find(What:=nm.Value, _
After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) 'Find Name inWorksheet1
If Not r Is Nothing Then 'if r is not nothing the name was found, go
on to copy
'***The next statement caused the problem, it was set to look for an
exact match "xlWhole"
Set hrs = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
After:=r, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) 'Find payroll
'Upon seeing that error I realized I had no error trapping for when there
is no "Reported..." after
'the name so now we check hrs to see if it was found
If Not hrs Is Nothing Then
'Resize the Variable range "hrs" to include the 8 columns tothe
Right
'then copy all those cells to the next empty line on ws2
'Using nRow makes sure that the name and hours get copied onto
the correct row
'in case there was a blank spot in Column B's data
nRow = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row + 1
nm.Copy ws2.Range("A" & nRow)
hrs.Resize(1, 9).Copy ws2.Range("B" & nRow)
End If
End If
Next
End Sub
Sub Resorting1()
'
' Resorting1 Macro
' Macro recorded 10/25/2006 by MartÃn Hinojosa
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Palatino Linotype"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveCell.FormulaR1C1 = "Associate Name"
Range("A1").Select
Selection.Font.Bold = True
Range("C:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B1").Select
ActiveCell.FormulaR1C1 = "Pay Period"
Columns("B:B").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Response = InputBox("Enter Pay Period")
Range("B2", Range("B2").End(xlDown)) = Response
Columns("B:B").EntireColumn.AutoFit
Range("A1").Select
Sheets("TMSDL").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Sheets("DATA").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "Regular"
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Overtime"
Columns("D
").EntireColumn.AutoFit
Range("E1").Select
ActiveCell.FormulaR1C1 = "Vaction"
Columns("E:E").EntireColumn.AutoFit
Range("F1").Select
ActiveCell.FormulaR1C1 = "Sick"
Columns("F:F").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "Personal"
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "Holiday"
Columns("H:H").EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = "Floating"
Columns("I:I").EntireColumn.AutoFit
Range("J1").Select
ActiveCell.FormulaR1C1 = "Grand Total"
Columns("J:J").EntireColumn.AutoFit
Application.WindowState = xlNormal
ActiveWindow.ScrollColumn = 2
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-1
Application.Width = 524.25
Application.Height = 552.75
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 319
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 375
ActiveWindow.ScrollRow = 381
ActiveWindow.ScrollRow = 384
ActiveWindow.ScrollRow = 390
ActiveWindow.ScrollRow = 394
ActiveWindow.ScrollRow = 398
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 406
ActiveWindow.ScrollRow = 408
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 412
ActiveWindow.ScrollRow = 408
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 398
ActiveWindow.ScrollRow = 394
ActiveWindow.ScrollRow = 386
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 377
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 346
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 340
ActiveWindow.ScrollRow = 338
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 332
ActiveWindow.ScrollRow = 330
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 319
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 311
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 297
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 291
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("J:J").Select
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Palatino Linotype"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7]:RC[-1])"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7]:RC[-1])"
Range("J2:J3").Select
Selection.AutoFill Destination:=Range("J2:J65536"), Type:=xlFillDefault
Range("J2:J1000").Select
Range("J1002").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
Sub RUNFRIST()
'
' RUNFRIST Macro
' Macro recorded 10/19/2006 by MartÃn Hinojosa
'
'
Sheets("TMSDL").Select
Sheets.Add
Sheets("TMSDL").Select
Sheets.Add
Sheets("TMSDL").Select
Sheets("TMSDL").Move Before:=Sheets(1)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "NAME"
Range("A1").Select
Windows("PERSONAL.XLS").Visible = True
Columns("A:A").Select
Selection.Copy
Windows("TMSDL.XLS").Activate
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="NameList", RefersToR1C1:="=NAME!C1"
Range("A2").Select
Sheets("TMSDL").Select
Application.Run "PERSONAL.XLS!TMSpart1"
Application.Run "PERSONAL.XLS!FindPayRoll"
Sheets("DATA").Select
Windows("PERSONAL.XLS").Activate
ActiveWindow.Visible = False
Application.Run "PERSONAL.XLS!Resorting1"
End Sub
Option Explicit
Sub FinalStep()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim r1 As Range
Dim r2 As Range
Dim r1c As Range
Dim cnt As Long
Dim ws As Worksheet
Dim Aws As Object 'ActiveSheet allows me to put the user back where they
were.
Dim Aws2 As Object
Set wb1 = Workbooks("TMSDL.xls")
Set wb2 = Workbooks("TMSFINAL.xls")
Set Aws = ActiveSheet 'Get user location
wb2.Activate 'Get user location in "TMSFINAL"
Set Aws2 = ActiveSheet
Application.StatusBar = "Moving Names to correct sheet. Please be patient."
Application.ScreenUpdating = False
'Change "A1" to the starting cell in your list. Note there are 2
instances of "A1"
Set r1 = Range(wb1.Worksheets("Data").Range("A2"), _
wb1.Worksheets("Data").Range("A2").End(xlDown))
Application.CutCopyMode = False
For cnt = r1.Rows.Count To 1 Step -1
Set r1c = r1.Cells(cnt, 1)
' r1.Cells(cnt, 1).Resize(1, 11).Copy
For Each ws In wb2.Worksheets
Set r2 = Nothing
Set r2 = ws.Cells.Find(r1c.Value)
If Not r2 Is Nothing Then
Application.CutCopyMode = False
r2.Offset(-1).EntireRow.Insert Shift:=xlDown, CopyOrigin:
=xlFormatFromRightOrBelow
r1.Cells(cnt, 1).Resize(1, 10).Copy
r2.Offset(-1).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
r1.Cells(cnt, 1).EntireRow.Delete
Exit For
End If
Next
For Each ws In wb2.Worksheets
ws.Activate
ws.Range("A1").Select 'I hate leaving multiple cells selected
Next
Aws2.Activate 'Move user back to where they were in "TMSFINAL"
Application.CutCopyMode = False
Next
Aws.Activate 'Move user back to where they were before running macro
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Sub FINISHEDTMS()
'
' FINISHEDTMS Macro
' Macro recorded 11/24/2006 by MHINOJO1
'
'
Application.Run "PERSONAL.XLS!RUNFRIST"
Application.WindowState = xlMaximized
Columns("A:B").Select
Range("A194").Activate
Selection.Font.Bold = True
Range("A194").Select
Application.Run "PERSONAL.XLS!FinalStep"
Windows("TMSFINAL.xls").Activate
ActiveSheet.Outline.ShowLevels RowLevels:=2
Sheets("Jeanette").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Sheets("Chandra").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Sheets("Becky").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Sheets("Heather").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Sheets("Sandra").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveWindow.SmallScroll Down:=-261
Sheets("Heather").Select
ActiveWindow.SmallScroll Down:=-138
Sheets("Becky").Select
ActiveWindow.SmallScroll Down:=-228
Sheets("Chandra").Select
ActiveWindow.SmallScroll Down:=-204
Range("A34").Select
Sheets("Becky").Select
Range("A34").Select
Sheets("Heather").Select
Range("A35").Select
Sheets("Sandra").Select
Range("A34").Select
Sheets("Jeanette").Select
ActiveWindow.SmallScroll Down:=-21
Range("A27").Select
Sheets("Amie").Select
ActiveWindow.SmallScroll Down:=-117
Range("A28").Select
Windows("TMSDL.XLS").Activate
ActiveWorkbook.Password = "lee"
ActiveWorkbook.WritePassword = "lee"
Windows("TMSFINAL.XLS").Activate
ActiveWorkbook.Password = "lee"
ActiveWorkbook.WritePassword = "lee"
End Sub
Why not post the macro (or the relevant portion) in a message, along
with your explanation?
[quoted text clipped - 5 lines]
