I put a workbook together withe several sheets and macrs/VBA Code.

Runs fine on my machine....

I email it out to a few friedns and it does not work on thier
machines.

I had them set macro security to prompt when macros are going to be
run. Still nothing.

After some research, I think the trouble is that EXCEL has to be told
to look in ThisWorklbook first.

How do I do that?

Do I have to adjsut the actual code?

Here is my code:

Option Explicit
Sub Main()

Call RawFilter
Call GetScenarioTurns
Call FormatWorkingData
Call ProcessData

End Sub

Sub RawFilter()

Sheets("Working Data").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Sheets("Filter Criteria").Rows("1:3"),
CopyToRange:=Range("A1"), Unique _
:=False
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select

End Sub

Sub GetScenarioTurns()

Dim myFormula As String
Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Working Data")

myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))),""UNKNOWN""," _
& "IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))>=35," _
& "--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))<=1859)," _
& "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
& "REPT("" "",100)),100))),""UNKNOWN""))"

With wks
.Range("i1").EntireColumn.Insert
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Range("i2:i" & LastRow).Formula = myFormula
.Range("G2:G" & LastRow).Replace "TS", "Battleground"
.Range("Q2:AH" & LastRow).Replace ". dummy3 dummy3, ././., ",
""
.Range("Q2:AH" & LastRow).Replace ". . ., ././., .", ""
End With

End Sub

Sub FormatWorkingData()

Application.ScreenUpdating = False
Cells.Select
Selection.RowHeight = 25
Rows("1:1").Select
Selection.RowHeight = 40
With Selection.Font
.Name = "Bookman Old Style"
.FontStyle = "Regular"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Cells.Select
With Selection.Font
.Name = "Bookman Old Style"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("A1:AH1").Select
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells.Select
Selection.Columns.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
ActiveWindow.SmallScroll ToRight:=8
Columns("K:K").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Columns("L:L").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("J:J").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("M:N").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Selection.NumberFormat = "ddmmmyy"
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Application.ScreenUpdating = True
Range("I1") = "Length"
Columns("I:I").Select
Selection.Columns.AutoFit

End Sub

Sub ProcessData()

Const TEST_COLUMN As String = "D"
Dim i As Long, j As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Working Data")

With wks
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

DoSwap i, 3
For j = 16 To 27 Step 4

DoSwap i, j
Next j
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Private Sub DoSwap(ActiveRow As Long, ActiveCol As Long)

Dim tmp As Variant

With ActiveSheet.Cells(ActiveRow, ActiveCol)

If .Offset(0, 1).Value Like "*AoS" Then

tmp = .Offset(0, 1).Value
.Offset(0, 1).Value = .Offset(0, 3).Value
.Offset(0, 3).Value = tmp
tmp = .Value
.Value = .Offset(0, 2).Value
.Offset(0, 2).Value = tmp
End If
End With

End Sub

Hi John,

If these macros are included in the workbooks you're emailing, there's no reason I can see they shouldn't run just as they do for
you. Having said that, are you sure the macros are included in that workbook and not in another workbook (eg your 'Personal.xls'
workbook)?

As an aside, your code makes a lot of seleections, which are inefficient. Specifying the ranges, without selecting them, is much
more efficient. For example your 'RawFilter' sub could be recoded as:
Sub RawFilter()
Sheets("Working Data").Cells.ClearContents
Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Sheets("Filter Criteria").Rows("1:3"), CopyToRange:=Range("A1"), Unique _
:=False
Sheets("Working Data").Columns.AutoFit
End Sub
Also, your 'FormatWorkingData' sub has a lot of redundant code in it - it looks like someone's recorded a macro to record some
fomatting and changed their mind part way through, so the same range gets formatted with '.HorizontalAlignment = xlGeneral' then
with '.HorizontalAlignment = xlCenter' - only the second routine is needed.


--
Cheers
macropod
[MVP - Microsoft Word]


"John Corbin" <JohnHabsFan@gmail.com> wrote in message news:41599be4-4e33-4977-b072-c0609f19b673@m45g2000hsb.googlegroups.com...
>I put a workbook together withe several sheets and macrs/VBA Code.
>
> Runs fine on my machine....
>
> I email it out to a few friedns and it does not work on thier
> machines.
>
> I had them set macro security to prompt when macros are going to be
> run. Still nothing.
>
> After some research, I think the trouble is that EXCEL has to be told
> to look in ThisWorklbook first.
>
> How do I do that?
>
> Do I have to adjsut the actual code?
>
> Here is my code:
>
> Option Explicit
> Sub Main()
>
> Call RawFilter
> Call GetScenarioTurns
> Call FormatWorkingData
> Call ProcessData
>
> End Sub
>
> Sub RawFilter()
>
> Sheets("Working Data").Select
> Cells.Select
> Selection.Delete Shift:=xlUp
> Range("A1").Select
> Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy,
> CriteriaRange _
> :=Sheets("Filter Criteria").Rows("1:3"),
> CopyToRange:=Range("A1"), Unique _
> :=False
> Cells.Select
> Selection.Columns.AutoFit
> Range("A1").Select
>
> End Sub
>
> Sub GetScenarioTurns()
>
> Dim myFormula As String
> Dim wks As Worksheet
> Dim LastRow As Long
>
> Set wks = Worksheets("Working Data")
>
> myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> & "REPT("" "",100)),100))),""UNKNOWN""," _
> & "IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> & "REPT("" "",100)),100))>=35," _
> & "--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> & "REPT("" "",100)),100))<=1859)," _
> & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> & "REPT("" "",100)),100))),""UNKNOWN""))"
>
> With wks
> .Range("i1").EntireColumn.Insert
> LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
> .Range("i2:i" & LastRow).Formula = myFormula
> .Range("G2:G" & LastRow).Replace "TS", "Battleground"
> .Range("Q2:AH" & LastRow).Replace ". dummy3 dummy3, ././., ",
> ""
> .Range("Q2:AH" & LastRow).Replace ". . ., ././., .", ""
> End With
>
> End Sub
>
> Sub FormatWorkingData()
>
> Application.ScreenUpdating = False
> Cells.Select
> Selection.RowHeight = 25
> Rows("1:1").Select
> Selection.RowHeight = 40
> With Selection.Font
> .Name = "Bookman Old Style"
> .FontStyle = "Regular"
> .Size = 18
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> End With
> Cells.Select
> With Selection.Font
> .Name = "Bookman Old Style"
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> End With
> Range("A1:AH1").Select
> With Selection.Interior
> .ColorIndex = 43
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> Cells.Select
> Selection.Columns.AutoFit
> Columns("A:A").Select
> Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> ActiveWindow.SmallScroll ToRight:=8
> Columns("K:K").Select
> Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> Columns("L:L").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("I:I").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("J:J").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("M:N").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("B:B").Select
> Selection.NumberFormat = "ddmmmyy"
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("C:C").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("E:E").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> With Selection
> .HorizontalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Application.ScreenUpdating = True
> Range("I1") = "Length"
> Columns("I:I").Select
> Selection.Columns.AutoFit
>
> End Sub
>
> Sub ProcessData()
>
> Const TEST_COLUMN As String = "D"
> Dim i As Long, j As Long
> Dim LastRow As Long
> Dim wks As Worksheet
>
> Set wks = Worksheets("Working Data")
>
> With wks
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
> End With
>
> With ActiveSheet
>
> LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
> For i = 2 To LastRow
>
> DoSwap i, 3
> For j = 16 To 27 Step 4
>
> DoSwap i, j
> Next j
> Next i
>
> End With
>
> With Application
>
> .Calculation = xlCalculationAutomatic
> .ScreenUpdating = True
> End With
>
> End Sub
>
> Private Sub DoSwap(ActiveRow As Long, ActiveCol As Long)
>
> Dim tmp As Variant
>
> With ActiveSheet.Cells(ActiveRow, ActiveCol)
>
> If .Offset(0, 1).Value Like "*AoS" Then
>
> tmp = .Offset(0, 1).Value
> .Offset(0, 1).Value = .Offset(0, 3).Value
> .Offset(0, 3).Value = tmp
> tmp = .Value
> .Value = .Offset(0, 2).Value
> .Offset(0, 2).Value = tmp
> End If
> End With
>
> End Sub
>

On Jul 9, 2:22*am, "macropod" <macro...@invalid.invalid> wrote:
> Hi John,
>
> If these macros are included in the workbooks you're emailing, there's noreason I can see they shouldn't run just as they do for
> you. Having said that, are you sure the macros are included in that workbook and not in another workbook (eg your 'Personal.xls'
> workbook)?
>
> As an aside, your code makes a lot of seleections, which are inefficient.Specifying the ranges, without selecting them, is much
> more efficient. For example your 'RawFilter' sub could be recoded as:
> Sub RawFilter()
> * * Sheets("Working Data").Cells.ClearContents
> * * Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
> * * * * :=Sheets("Filter Criteria").Rows("1:3"), CopyToRange:=Range("A1"), Unique _
> * * * * :=False
> * * Sheets("Working Data").Columns.AutoFit
> End Sub
> Also, your 'FormatWorkingData' sub has a lot of redundant code in it - itlooks like someone's recorded a macro to record some
> fomatting and changed their mind part way through, so the same range getsformatted with '.HorizontalAlignment = xlGeneral' then
> with '.HorizontalAlignment = xlCenter' - only the second routine is needed.
>
> --
> Cheers
> macropod
> [MVP - Microsoft Word]
>
>
>
> "John Corbin" <JohnHabs...@gmail.com> wrote in messagenews:41599be4-4e33-4977-b072-c0609f19b673@m45g2000hsb.googlegroups.com...
> >I put a workbook together withe several sheets and macrs/VBA Code.
>
> > Runs fine on my machine....
>
> > I email it out to a few friedns and it does not work on thier
> > machines.
>
> > I had them set macro security to prompt when macros are going to be
> > run. *Still nothing.
>
> > After some research, I think the trouble is that EXCEL has to be told
> > to look in ThisWorklbook first.
>
> > How do I do that?
>
> > Do I have to adjsut the actual code?
>
> > Here is my code:
>
> > Option Explicit
> > Sub Main()
>
> > * *Call RawFilter
> > * *Call GetScenarioTurns
> > * *Call FormatWorkingData
> > * *Call ProcessData
>
> > End Sub
>
> > Sub RawFilter()
>
> > * *Sheets("Working Data").Select
> > * *Cells.Select
> > * *Selection.Delete Shift:=xlUp
> > * *Range("A1").Select
> > * *Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy,
> > CriteriaRange _
> > * * * *:=Sheets("Filter Criteria").Rows("1:3"),
> > CopyToRange:=Range("A1"), Unique _
> > * * * *:=False
> > * *Cells.Select
> > * *Selection.Columns.AutoFit
> > * *Range("A1").Select
>
> > End Sub
>
> > Sub GetScenarioTurns()
>
> > * *Dim myFormula As String
> > * *Dim wks As Worksheet
> > * *Dim LastRow As Long
>
> > * *Set wks = Worksheets("Working Data")
>
> > * * myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > * * * * * * * *& "REPT("" "",100)),100))),""UNKNOWN"","_
> > * * * * * * * *& "IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > * * * * * * * *& "REPT("" "",100)),100))>=35," _
> > * * * * * * * *& "--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > * * * * * * * *& "REPT("" "",100)),100))<=1859)," _
> > * * * * * * * *& "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > * * * * * * * *& "REPT("" "",100)),100))),""UNKNOWN""))"
>
> > * *With wks
> > * * * *.Range("i1").EntireColumn.Insert
> > * * * * LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
> > * * * *.Range("i2:i" & LastRow).Formula = myFormula
> > * * * *.Range("G2:G" & LastRow).Replace "TS", "Battleground"
> > * * * *.Range("Q2:AH" & LastRow).Replace ". dummy3 dummy3, ././.., ",
> > ""
> > * * * *.Range("Q2:AH" & LastRow).Replace ". . ., ././., .", ""
> > * *End With
>
> > End Sub
>
> > Sub FormatWorkingData()
>
> > * *Application.ScreenUpdating = False
> > * *Cells.Select
> > * *Selection.RowHeight = 25
> > * *Rows("1:1").Select
> > * *Selection.RowHeight = 40
> > * *With Selection.Font
> > * * * *.Name = "Bookman Old Style"
> > * * * *.FontStyle = "Regular"
> > * * * *.Size = 18
> > * * * *.Strikethrough = False
> > * * * *.Superscript = False
> > * * * *.Subscript = False
> > * * * *.OutlineFont = False
> > * * * *.Shadow = False
> > * * * *.Underline = xlUnderlineStyleNone
> > * * * *.ColorIndex = 1
> > * *End With
> > * *Cells.Select
> > * *With Selection.Font
> > * * * *.Name = "Bookman Old Style"
> > * * * *.Strikethrough = False
> > * * * *.Superscript = False
> > * * * *.Subscript = False
> > * * * *.OutlineFont = False
> > * * * *.Shadow = False
> > * * * *.Underline = xlUnderlineStyleNone
> > * *End With
> > * *Range("A1:AH1").Select
> > * *With Selection.Interior
> > * * * *.ColorIndex = 43
> > * * * *.Pattern = xlSolid
> > * * * *.PatternColorIndex = xlAutomatic
> > * *End With
> > * *Cells.Select
> > * *Selection.Columns.AutoFit
> > * *Columns("A:A").Select
> > * *Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> > * *ActiveWindow.SmallScroll ToRight:=8
> > * *Columns("K:K").Select
> > * *Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> > * *Columns("L:L").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("I:I").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("J:J").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("M:N").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("B:B").Select
> > * *Selection.NumberFormat = "ddmmmyy"
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("C:C").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Columns("E:E").Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlGeneral
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *With Selection
> > * * * *.HorizontalAlignment = xlCenter
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Application.ScreenUpdating = True
> > * *Range("I1") = "Length"
> > * *Columns("I:I").Select
> > * *Selection.Columns.AutoFit
>
> > End Sub
>
> > Sub ProcessData()
>
> > * *Const TEST_COLUMN As String = "D"
> > * *Dim i As Long, j As Long
> > * *Dim LastRow As Long
> > * *Dim wks As Worksheet
>
> > * *Set wks = Worksheets("Working Data")
>
> > * *With wks
> > * * * *Application.ScreenUpdating = False
> > * * * *Application.Calculation = xlCalculationManual
> > * *End With
>
> > * *With ActiveSheet
>
> > * * * *LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
> > * * * *For i = 2 To LastRow
>
> > * * * * * *DoSwap i, 3
> > * * * * * *For j = 16 To 27 Step 4
>
> > * * * * * * * *DoSwap i, j
> > * * * * * *Next j
> > * * * *Next i
>
> > * *End With
>
> > * *With Application
>
> > * * * *.Calculation =
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

Thanks

In know the macro code looks awkward.. I am in the process of fixing
it... Thansk for
the tips...

on another topic..

Is any of the code I posted a problem for earlier versions of EXCEL?

The above code was composed in EXCEL 2003....

Hi John,

I don't see anything there that wouldn't work on Excel 2000 and later (I actually tried it with Excel 2000 and it seemed to work
OK). I suspect the same applies to Excel 97.

--
Cheers
macropod
[MVP - Microsoft Word]


"John Corbin" <JohnHabsFan@gmail.com> wrote in message news:cec28100-b059-44cf-a384-df54b73edea0@b1g2000hsg.googlegroups.com...
On Jul 9, 2:22 am, "macropod" <macro...@invalid.invalid> wrote:
> Hi John,
>
> If these macros are included in the workbooks you're emailing, there's no reason I can see they shouldn't run just as they do for
> you. Having said that, are you sure the macros are included in that workbook and not in another workbook (eg your 'Personal.xls'
> workbook)?
>
> As an aside, your code makes a lot of seleections, which are inefficient. Specifying the ranges, without selecting them, is much
> more efficient. For example your 'RawFilter' sub could be recoded as:
> Sub RawFilter()
> Sheets("Working Data").Cells.ClearContents
> Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
> :=Sheets("Filter Criteria").Rows("1:3"), CopyToRange:=Range("A1"), Unique _
> :=False
> Sheets("Working Data").Columns.AutoFit
> End Sub
> Also, your 'FormatWorkingData' sub has a lot of redundant code in it - it looks like someone's recorded a macro to record some
> fomatting and changed their mind part way through, so the same range gets formatted with '.HorizontalAlignment = xlGeneral' then
> with '.HorizontalAlignment = xlCenter' - only the second routine is needed.
>
> --
> Cheers
> macropod
> [MVP - Microsoft Word]
>
>
>
> "John Corbin" <JohnHabs...@gmail.com> wrote in messagenews:41599be4-4e33-4977-b072-c0609f19b673@m45g2000hsb.googlegroups.com...
> >I put a workbook together withe several sheets and macrs/VBA Code.
>
> > Runs fine on my machine....
>
> > I email it out to a few friedns and it does not work on thier
> > machines.
>
> > I had them set macro security to prompt when macros are going to be
> > run. Still nothing.
>
> > After some research, I think the trouble is that EXCEL has to be told
> > to look in ThisWorklbook first.
>
> > How do I do that?
>
> > Do I have to adjsut the actual code?
>
> > Here is my code:
>
> > Option Explicit
> > Sub Main()
>
> > Call RawFilter
> > Call GetScenarioTurns
> > Call FormatWorkingData
> > Call ProcessData
>
> > End Sub
>
> > Sub RawFilter()
>
> > Sheets("Working Data").Select
> > Cells.Select
> > Selection.Delete Shift:=xlUp
> > Range("A1").Select
> > Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy,
> > CriteriaRange _
> > :=Sheets("Filter Criteria").Rows("1:3"),
> > CopyToRange:=Range("A1"), Unique _
> > :=False
> > Cells.Select
> > Selection.Columns.AutoFit
> > Range("A1").Select
>
> > End Sub
>
> > Sub GetScenarioTurns()
>
> > Dim myFormula As String
> > Dim wks As Worksheet
> > Dim LastRow As Long
>
> > Set wks = Worksheets("Working Data")
>
> > myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > & "REPT("" "",100)),100))),""UNKNOWN""," _
> > & "IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > & "REPT("" "",100)),100))>=35," _
> > & "--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > & "REPT("" "",100)),100))<=1859)," _
> > & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _
> > & "REPT("" "",100)),100))),""UNKNOWN""))"
>
> > With wks
> > .Range("i1").EntireColumn.Insert
> > LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
> > .Range("i2:i" & LastRow).Formula = myFormula
> > .Range("G2:G" & LastRow).Replace "TS", "Battleground"
> > .Range("Q2:AH" & LastRow).Replace ". dummy3 dummy3, ././., ",
> > ""
> > .Range("Q2:AH" & LastRow).Replace ". . ., ././., .", ""
> > End With
>
> > End Sub
>
> > Sub FormatWorkingData()
>
> > Application.ScreenUpdating = False
> > Cells.Select
> > Selection.RowHeight = 25
> > Rows("1:1").Select
> > Selection.RowHeight = 40
> > With Selection.Font
> > .Name = "Bookman Old Style"
> > .FontStyle = "Regular"
> > .Size = 18
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = 1
> > End With
> > Cells.Select
> > With Selection.Font
> > .Name = "Bookman Old Style"
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > End With
> > Range("A1:AH1").Select
> > With Selection.Interior
> > .ColorIndex = 43
> > .Pattern = xlSolid
> > .PatternColorIndex = xlAutomatic
> > End With
> > Cells.Select
> > Selection.Columns.AutoFit
> > Columns("A:A").Select
> > Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> > ActiveWindow.SmallScroll ToRight:=8
> > Columns("K:K").Select
> > Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
> > Columns("L:L").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("I:I").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("J:J").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("M:N").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("B:B").Select
> > Selection.NumberFormat = "ddmmmyy"
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("C:C").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("E:E").Select
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .WrapText = False
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Application.ScreenUpdating = True
> > Range("I1") = "Length"
> > Columns("I:I").Select
> > Selection.Columns.AutoFit
>
> > End Sub
>
> > Sub ProcessData()
>
> > Const TEST_COLUMN As String = "D"
> > Dim i As Long, j As Long
> > Dim LastRow As Long
> > Dim wks As Worksheet
>
> > Set wks = Worksheets("Working Data")
>
> > With wks
> > Application.ScreenUpdating = False
> > Application.Calculation = xlCalculationManual
> > End With
>
> > With ActiveSheet
>
> > LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
> > For i = 2 To LastRow
>
> > DoSwap i, 3
> > For j = 16 To 27 Step 4
>
> > DoSwap i, j
> > Next j
> > Next i
>
> > End With
>
> > With Application
>
> > .Calculation =
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

Thanks

In know the macro code looks awkward.. I am in the process of fixing
it... Thansk for
the tips...

on another topic..

Is any of the code I posted a problem for earlier versions of EXCEL?

The above code was composed in EXCEL 2003....