Excel Macro generating single random output

Question

I recorded a macro that runs through a bunch of formatting and splicing of data. It basically formats the data page, then splices it into months. The data roughly looks like this:

Column A    ...  Column D ... Column H    Column I    Jan    Feb ...  Nov     Dec
   -              00645           -        budget    45.2    32.8    -15.0   100.00

Basically it fills in entries for all of column A (all entries are "LINE"), renames header on columns B, C, D, H, and I, then copys columns A-J and moves it to a tab created just for the specific month, then deletes column J so the next month will always be in column J and continues repeating till all months are parsed out. Its a pretty easy macro, mostly recorded, but its behaving strangely.

The issue is that once it runs, it randomly inputs the value that is only supposed to be in column A ("LINE") to a single cell somewhere else in the file. It seems semi-random which cell it is. If you put in data from File A, no matter how many times you run the macro "LINE" will show up in the same cell. But it will be a different cell for Files B, C, D and so on. There is nothing fundamentially different about the cell it does this in when compared to the other cells (same entry, same formatting, etc). I have no idea why its doing this. Below is my code:

Sub Format_Upload()

Format_Upload Macro



ActiveCell.FormulaR1C1 = "LINE"
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2").Copy Range("A2").Resize(LastRow - 1)
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Dept ID"
Range("C1").Select
ActiveCell.FormulaR1C1 = "MSUPLOAD1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "01/01/14"
Range("I1").Select
ActiveCell.FormulaR1C1 = "01/01/14"
Range("D1").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("I1").Select
Selection.NumberFormat = "mm/dd/yy;@"
Cells.Select
With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Columns("J:U").Select
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.NumberFormat = "0.00"
Columns("A:J").Select
Selection.Copy
Sheets("JAN").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Data").Select
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("FEB").Select
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "2/1/2014"
Range("H1").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("MAR").Select
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "3/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "3/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("APR").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "4/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "4/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Range("J1").Activate
Selection.Copy
Sheets("MAY").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "5/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("JUN").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "6/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "6/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("JUL").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "7/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "7/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("AUG").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "8/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "8/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("SEP").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "9/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "9/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Selection.Copy
Sheets("OCT").Select
Sheets("Data").Select
Columns("A:J").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OCT").Select
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "10/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "10/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("NOV").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "11/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "11/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
Selection.Copy
Sheets("DEC").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "12/1/2014"
Range("I1").Select
ActiveCell.FormulaR1C1 = "12/1/2014"
Range("I2").Select
Sheets("Data").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft

Sheets("FEB").Select
ActiveCell.FormulaR1C1 = "MSFEB"
Sheets("MAR").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSMAR"
Sheets("APR").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSAPR"
Sheets("MAY").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSMAY"
Sheets("JUN").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSJUN"
Sheets("JUL").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSJUL"
Sheets("AUG").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSAUG"
Sheets("SEP").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSSEP"
Sheets("OCT").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSOCT"
Sheets("NOV").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSNOV"
Sheets("DEC").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "MSDEC"
Range("H2").Select

End Sub

I do not see anything in the code that explains this anomaly. Any ideas as to what's causing this issue? '

EDIT: The strange cell always appears on the first tab (named "JAN"), but its location varies.

Thank you. Since I wasn't sure what the problem was, I couldn't really find any relevant prior questions on here. This is helpful. Thanks! — Mike L, Dec 20 '13 at 15:26

score 2 · Accepted Answer · answered Dec 20 '13 at 15:19

2

ActiveCell.FormulaR1C1 = "LINE"

This is putting "LINE" in whichever cell you have selected before running it.

Change that to something else

answered Dec 20 '13 at 15:19

Julien Marrec

11,605
4
46
63

Excel Macro generating single random output

1 Answers1