J
Jim Berglund
1. I am using 2 worksheets in the same Excel XP file, (YTD BD, and Analyzer)
and swiching back and forth between them. I frequently get Error 1004 codes
when I'm trying to select a range. Could someone please tell me why?
The example follows the second question... (the error occurs between the
asterisks at the second select line). The problem began when I attempted to
merge two subroutines.
__________________________________________________________________________
2. If I wanted to use the following statement using 'With' and 'End With'
(and I really don't know why I would), is it necessary (or advisable) to
include the Sort statement between these?
Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
as in...
With ActiveSheets
.Range(Cells(2, 1), Cells(numberofRows, numberofColumns)).Select
' should I put the 'End With' here?...
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'or here?...
Many Thanks,
Jim Berglund
_________________________________________________________________________
Private Sub CommandButton4_Click() 'Load Files
Dim i, j, k, x, y, numberofRows As Integer, numberofColumns, nextCol As
Integer
Application.ScreenUpdating = False
'Clear Data Sheet - YTD BD
Workbooks("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns("AA").Select
Selection.Clear
Selection.Delete Shift:=xlToLeft
'Import Data from Cognos-generated .xls files
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment
Replacement\EMMSReplacement3(EqptData).xls"
Workbooks("EMMSReplacement3(EqptData).xls").Activate
Sheets("Equipment Data").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
nextCol = numberofColumns + 2
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)LTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)YTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\Unit
Condition Report.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS LTD
Downtime.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 2
ActiveSheet.Rows("1:1").Select
Selection.Interior.ColorIndex = xlNone
Sheets("YTD BD").Select
ActiveSheet.Range("A63999").Select
Selection.Copy
ActiveSheet.Range("A1:AJ484").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Close original data files
Windows("EMMSReplacement3(EqptData).xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)LTD.xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)YTD.xls").Activate
ActiveWorkbook.Close
Windows("Unit Condition Report.xls").Activate
ActiveWorkbook.Close
Windows("EMMS LTD Downtime.xls").Activate
ActiveWorkbook.Close
'***************************************************************************
***********
Sheets("YTD BD").Activate 'Normalize
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
'The following is the problem line *******
Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'***************************************************************************
************
ActiveSheet.Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
ActiveSheet.Range(Cells(2, numberofColumns + 2), Cells(numberofRows,
numberofColumns + 6)).Select
Selection.Sort Key1:=Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 2).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 3), Cells(i,
numberofColumns + 6)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 20), Cells(j,
numberofColumns + 23)).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 8), Cells(numberofColumns + 8)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 8), Cells(numberofRows,
numberofColumns + 12)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 8),
Cells(numberofColumns + 12)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 8).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 9), Cells(i,
numberofColumns + 12)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 24), Cells(j,
numberofColumns + 27)).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 14), Cells(numberofColumns +
14)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 14), Cells(numberofRows,
numberofColumns + 15)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 14),
Cells(numberofColumns + 14)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 14).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 15).Select
Selection.Copy
Cells(j, numberofColumns + 28).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 17), Cells(numberofColumns +
17)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 17), Cells(numberofRows,
numberofColumns + 18)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 17),
Cells(numberofColumns + 17)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 17).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 18).Select
Selection.Copy
Cells(j, numberofColumns + 29).Select
ActiveSheet.Paste
End If
End With
Next
Sheets("YTD BD").Activate
'Create new Headings
Range(Cells(1, numberofColumns + 15), Cells(1, numberofColumns +
15)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 28).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 18), Cells(1, numberofColumns +
18)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 29).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 9), Cells(1, numberofColumns +
12)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 24), Cells(1, numberofColumns +
27)).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 3), Cells(1, numberofColumns +
6)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 20), Cells(1, numberofColumns +
23)).Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(1, numberofColumns + 1), Cells(numberofRows, numberofColumns
+ 19)).Select
Selection.Delete Shift:=xlToLeft
'Format Columns
' Columns("V:V").Select
' Selection.Copy
' Range(Cells(1, numberofColumns + 1), Cells(numberofRows,
numberofColumns + 20)).Select
' Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
' SkipBlanks:=False, Transpose:=False
' Application.CutCopyMode = False
'Insert and number a new top row
ActiveSheet.Range("A1").Select
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
Rows("1:1").Select
Selection.Insert Shift:=xlDown
For i = 1 To numberofColumns
Cells(1, i).Value = i
Next
'Copy data into the Analyzer
Range(Cells(1, 1), Cells(numberofRows, numberofColumns)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Copy
Sheets("Analyzer").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Sheets("YTD BD").Select
'Fill Variables
'Set Data for SuperTypes
Sheets("YTD BD").Select
ActiveSheet.Columns("BA:BK").Clear 'Clear a WorkSpace
ActiveSheet.Columns("H:H").Select 'Equipment SuperTypes
Selection.Copy
Range("BA1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Delete unnecessary Rows
'Sort Data and Create a new column with unique Supertype names
Selection.Sort Key1:=Range("BA1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BA1:BA2").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1
Range("BA1").Select
numberofRows = Cells(1, 53).CurrentRegion.Rows.Count
Range("BA1").Select
Selection.Copy
Cells(j, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 53).Value = Cells(j, 54).Value Then
i = i + 1
Else
Cells(i + 1, 53).Select
Selection.Copy
Cells(j + 1, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BC1").Select
numberofRows = Cells(1, 54).CurrentRegion.Rows.Count
Range(Cells(1, 54), Cells(1 + numberofRows, 54)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(4, 115).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Copy (7) Supertype Names into the Appropriate Columns
With ActiveSheet
.Range("CX12D108").Select
Selection.ClearContents
i = 102
j = 4
For i = 102 To 109
Cells(12, i).Value = Cells(j, 115).Value
j = j + 1
Next
End With
'Set Data for Facilities
Sheets("YTD BD").Select
ActiveSheet.Columns("W:W").Select 'Facilities
Selection.Copy
Range("BD1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("BD1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BD1").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1
Range("BD1").Select
numberofRows = Cells(1, 56).CurrentRegion.Rows.Count
Range("BD1").Select
Selection.Copy
Cells(1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 56).Value = Cells(j, 57).Value Then
i = i + 1
Else
Cells(i + 1, 56).Select
Selection.Copy
Cells(j + 1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BE1").Select
numberofRows = Cells(1, 57).CurrentRegion.Rows.Count
Range(Cells(1, 57), Cells(1 + numberofRows, 57)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(6, 110).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Set Data for Equipment Types
Sheets("YTD BD").Select
ActiveSheet.Columns("G:G").Select
Selection.Copy
Range("BG1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BG2").Select
Selection.Delete Shift:=xlUp
Application.CutCopyMode = False
Selection.Sort Key1:=Range("BG3"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i = 1
j = 1
Range("BH1").Select
numberofRows = Cells(1, 59).CurrentRegion.Rows.Count
Range("BH1").Select
Selection.Copy
Cells(1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 59).Value = Cells(j, 60).Value Then
i = i + 1
Else
Cells(i + 1, 59).Select
Selection.Copy
Cells(j + 1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BH1").Select
numberofRows = Cells(1, 60).CurrentRegion.Rows.Count
Range(Cells(2, 60), Cells(1 + numberofRows, 60)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(5, 112).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'End Sub
Sheets("Analyzer").Activate
ActiveSheet.Range("CX13D23").Select
Selection.ClearContents
ActiveSheet.Range("DG5").Select
i = 5 'First Row for Supertypes
k = 13 'First Row of Eqpt Types
j = 102 'Columns of Eqpt Types
numberofRows = ActiveCell.CurrentRegion.Rows.Count
For j = 102 To 108
For i = 5 To numberofRows
If Cells(i, 111).Value = Cells(12, j).Value Then
Cells(k, j).Value = Cells(i, 112).Value
k = k + 1
End If
Next
k = 13
i = 5
Next
ActiveSheet.Range("DI5I15").Select 'Select Create Equipment Type List
Selector
Selection.Copy
ActiveSheet.Range("DL5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("DF5F29").Select 'Select Create Facility List
Selector
Selection.Copy
ActiveSheet.Range("DM4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
and swiching back and forth between them. I frequently get Error 1004 codes
when I'm trying to select a range. Could someone please tell me why?
The example follows the second question... (the error occurs between the
asterisks at the second select line). The problem began when I attempted to
merge two subroutines.
__________________________________________________________________________
2. If I wanted to use the following statement using 'With' and 'End With'
(and I really don't know why I would), is it necessary (or advisable) to
include the Sort statement between these?
Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
as in...
With ActiveSheets
.Range(Cells(2, 1), Cells(numberofRows, numberofColumns)).Select
' should I put the 'End With' here?...
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'or here?...
Many Thanks,
Jim Berglund
_________________________________________________________________________
Private Sub CommandButton4_Click() 'Load Files
Dim i, j, k, x, y, numberofRows As Integer, numberofColumns, nextCol As
Integer
Application.ScreenUpdating = False
'Clear Data Sheet - YTD BD
Workbooks("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns("AA").Select
Selection.Clear
Selection.Delete Shift:=xlToLeft
'Import Data from Cognos-generated .xls files
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment
Replacement\EMMSReplacement3(EqptData).xls"
Workbooks("EMMSReplacement3(EqptData).xls").Activate
Sheets("Equipment Data").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
nextCol = numberofColumns + 2
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)LTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)YTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\Unit
Condition Report.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS LTD
Downtime.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 2
ActiveSheet.Rows("1:1").Select
Selection.Interior.ColorIndex = xlNone
Sheets("YTD BD").Select
ActiveSheet.Range("A63999").Select
Selection.Copy
ActiveSheet.Range("A1:AJ484").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Close original data files
Windows("EMMSReplacement3(EqptData).xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)LTD.xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)YTD.xls").Activate
ActiveWorkbook.Close
Windows("Unit Condition Report.xls").Activate
ActiveWorkbook.Close
Windows("EMMS LTD Downtime.xls").Activate
ActiveWorkbook.Close
'***************************************************************************
***********
Sheets("YTD BD").Activate 'Normalize
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
'The following is the problem line *******
Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'***************************************************************************
************
ActiveSheet.Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
ActiveSheet.Range(Cells(2, numberofColumns + 2), Cells(numberofRows,
numberofColumns + 6)).Select
Selection.Sort Key1:=Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 2).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 3), Cells(i,
numberofColumns + 6)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 20), Cells(j,
numberofColumns + 23)).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 8), Cells(numberofColumns + 8)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 8), Cells(numberofRows,
numberofColumns + 12)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 8),
Cells(numberofColumns + 12)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 8).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 9), Cells(i,
numberofColumns + 12)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 24), Cells(j,
numberofColumns + 27)).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 14), Cells(numberofColumns +
14)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 14), Cells(numberofRows,
numberofColumns + 15)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 14),
Cells(numberofColumns + 14)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 14).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 15).Select
Selection.Copy
Cells(j, numberofColumns + 28).Select
ActiveSheet.Paste
End If
End With
Next
Range(Cells(numberofColumns + 17), Cells(numberofColumns +
17)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 17), Cells(numberofRows,
numberofColumns + 18)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 17),
Cells(numberofColumns + 17)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For i = 2 To numberofRows
With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 17).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 18).Select
Selection.Copy
Cells(j, numberofColumns + 29).Select
ActiveSheet.Paste
End If
End With
Next
Sheets("YTD BD").Activate
'Create new Headings
Range(Cells(1, numberofColumns + 15), Cells(1, numberofColumns +
15)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 28).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 18), Cells(1, numberofColumns +
18)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 29).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 9), Cells(1, numberofColumns +
12)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 24), Cells(1, numberofColumns +
27)).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 3), Cells(1, numberofColumns +
6)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 20), Cells(1, numberofColumns +
23)).Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(1, numberofColumns + 1), Cells(numberofRows, numberofColumns
+ 19)).Select
Selection.Delete Shift:=xlToLeft
'Format Columns
' Columns("V:V").Select
' Selection.Copy
' Range(Cells(1, numberofColumns + 1), Cells(numberofRows,
numberofColumns + 20)).Select
' Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
' SkipBlanks:=False, Transpose:=False
' Application.CutCopyMode = False
'Insert and number a new top row
ActiveSheet.Range("A1").Select
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
Rows("1:1").Select
Selection.Insert Shift:=xlDown
For i = 1 To numberofColumns
Cells(1, i).Value = i
Next
'Copy data into the Analyzer
Range(Cells(1, 1), Cells(numberofRows, numberofColumns)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Copy
Sheets("Analyzer").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Sheets("YTD BD").Select
'Fill Variables
'Set Data for SuperTypes
Sheets("YTD BD").Select
ActiveSheet.Columns("BA:BK").Clear 'Clear a WorkSpace
ActiveSheet.Columns("H:H").Select 'Equipment SuperTypes
Selection.Copy
Range("BA1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Delete unnecessary Rows
'Sort Data and Create a new column with unique Supertype names
Selection.Sort Key1:=Range("BA1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BA1:BA2").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1
Range("BA1").Select
numberofRows = Cells(1, 53).CurrentRegion.Rows.Count
Range("BA1").Select
Selection.Copy
Cells(j, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 53).Value = Cells(j, 54).Value Then
i = i + 1
Else
Cells(i + 1, 53).Select
Selection.Copy
Cells(j + 1, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BC1").Select
numberofRows = Cells(1, 54).CurrentRegion.Rows.Count
Range(Cells(1, 54), Cells(1 + numberofRows, 54)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(4, 115).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Copy (7) Supertype Names into the Appropriate Columns
With ActiveSheet
.Range("CX12D108").Select
Selection.ClearContents
i = 102
j = 4
For i = 102 To 109
Cells(12, i).Value = Cells(j, 115).Value
j = j + 1
Next
End With
'Set Data for Facilities
Sheets("YTD BD").Select
ActiveSheet.Columns("W:W").Select 'Facilities
Selection.Copy
Range("BD1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("BD1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BD1").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1
Range("BD1").Select
numberofRows = Cells(1, 56).CurrentRegion.Rows.Count
Range("BD1").Select
Selection.Copy
Cells(1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 56).Value = Cells(j, 57).Value Then
i = i + 1
Else
Cells(i + 1, 56).Select
Selection.Copy
Cells(j + 1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BE1").Select
numberofRows = Cells(1, 57).CurrentRegion.Rows.Count
Range(Cells(1, 57), Cells(1 + numberofRows, 57)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(6, 110).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Set Data for Equipment Types
Sheets("YTD BD").Select
ActiveSheet.Columns("G:G").Select
Selection.Copy
Range("BG1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BG2").Select
Selection.Delete Shift:=xlUp
Application.CutCopyMode = False
Selection.Sort Key1:=Range("BG3"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i = 1
j = 1
Range("BH1").Select
numberofRows = Cells(1, 59).CurrentRegion.Rows.Count
Range("BH1").Select
Selection.Copy
Cells(1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False
For i = 1 To numberofRows
If Cells(i, 59).Value = Cells(j, 60).Value Then
i = i + 1
Else
Cells(i + 1, 59).Select
Selection.Copy
Cells(j + 1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BH1").Select
numberofRows = Cells(1, 60).CurrentRegion.Rows.Count
Range(Cells(2, 60), Cells(1 + numberofRows, 60)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(5, 112).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'End Sub
Sheets("Analyzer").Activate
ActiveSheet.Range("CX13D23").Select
Selection.ClearContents
ActiveSheet.Range("DG5").Select
i = 5 'First Row for Supertypes
k = 13 'First Row of Eqpt Types
j = 102 'Columns of Eqpt Types
numberofRows = ActiveCell.CurrentRegion.Rows.Count
For j = 102 To 108
For i = 5 To numberofRows
If Cells(i, 111).Value = Cells(12, j).Value Then
Cells(k, j).Value = Cells(i, 112).Value
k = k + 1
End If
Next
k = 13
i = 5
Next
ActiveSheet.Range("DI5I15").Select 'Select Create Equipment Type List
Selector
Selection.Copy
ActiveSheet.Range("DL5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("DF5F29").Select 'Select Create Facility List
Selector
Selection.Copy
ActiveSheet.Range("DM4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub