variable number of rows.
It is hard to fix code like this. Her is a much improved macro. I made of
made a fgew mistakes but you should easily be able to fix the problems.
Sub Macro7()
'
' Macro7 Macro
'
'
Columns("I:I").Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").EntireColumn.AutoFit
Range("I2").FormulaR1C1 = _
"=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")"
LastRow = Range("I" & rows.count).end(xlup).Row
Range("I2").Copy Destination:=Range("H2:H" & Lastrow)
Range("I1").Copy
Range("E1:E" & LastRow).PasteSpecial _
Paste:=xlPasteValues
Columns("I:I").Delete
Application.CutCopyMode = False
Columns("I:I").Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Range("I2").FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])"
Range("I2").Copy Destination:=Range("H2:H" & LastRow)
Range("I1:I" & LastRow).FormulaR1C1 = "DEST. FILE"
Range("H1").copy destination:=Range("H1:H" & LastRow)
Columns("I:I").Delete
Columns("W:W").Cut
Columns("A:A").Paste
Columns("T:T").Replace _
What:="ACROBAT_DEFAULT", _
Replacement:="NEW_WINDOW", _
LookAt:=xlPart
Rows(Lastrow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
set FirstBlankCell = Columns("A:A").SpecialCells(xlCellTypeBlanks)
set LastCell = ActiveCell.SpecialCells(xlLastCell)
Range(FirstBlankCell, LastCell).ClearContents
End Sub
"krigger228" wrote:
I have created this macro but it cuts off if I have too many rows. Is there a
way to make it run until it hits a blank cell in another column? Here is the
code I have now.
feel free to e-mail me if you need more information.
Sub Macro7()
'
' Macro7 Macro
'
'
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").EntireColumn.AutoFit
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")"
Range("I2").Select
Selection.Copy
Range("H2").Select
Selection.End(xlDown).Select
Range("I60487").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("A60486").Select
Selection.End(xlUp).Select
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ACTION"
Range("I1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 60216
ActiveWindow.ScrollRow = 59474
ActiveWindow.ScrollRow = 57743
ActiveWindow.ScrollRow = 55270
ActiveWindow.ScrollRow = 51561
ActiveWindow.ScrollRow = 47109
ActiveWindow.ScrollRow = 42658
ActiveWindow.ScrollRow = 38578
ActiveWindow.ScrollRow = 34869
ActiveWindow.ScrollRow = 31407
ActiveWindow.ScrollRow = 28068
ActiveWindow.ScrollRow = 25101
ActiveWindow.ScrollRow = 23122
ActiveWindow.ScrollRow = 20402
ActiveWindow.ScrollRow = 18919
ActiveWindow.ScrollRow = 17558
ActiveWindow.ScrollRow = 16198
ActiveWindow.ScrollRow = 14838
ActiveWindow.ScrollRow = 13602
ActiveWindow.ScrollRow = 12365
ActiveWindow.ScrollRow = 11376
ActiveWindow.ScrollRow = 10263
ActiveWindow.ScrollRow = 9522
ActiveWindow.ScrollRow = 8409
ActiveWindow.ScrollRow = 7667
ActiveWindow.ScrollRow = 6801
ActiveWindow.ScrollRow = 5936
ActiveWindow.ScrollRow = 4699
ActiveWindow.ScrollRow = 3216
ActiveWindow.ScrollRow = 1732
ActiveWindow.ScrollRow = 372
ActiveWindow.ScrollRow = 1
Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])"
Range("I2").Select
Selection.Copy
Range("H2").Select
Selection.End(xlDown).Select
Range("I60487").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "DEST. FILE"
Range("I1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlUp).Select
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=9
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=1
Columns("W:W").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-1
Columns("A:A").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("T2").Select
Selection.Copy
Columns("T:T").Select
Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Range("T3").Select
Application.CutCopyMode = False
Range("T1").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
Selection.End(xlDown).Select
Range("A1").Select
Selection.End(xlDown).Select
Rows("60488:60488").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A1").Select
End Sub
|