Macro changes formulas on destination worksheet

[Karen]

I have to run a macro to separate an account number into 7 columns. I used
the destination columns to which the data was spread for the formulas in the
destination workbook. I have 28 or 35 days in the period and a new payroll
sheet is downloaded each day and pasted into the workbook on a separate tab.
I have 35 blank tabs on a master workbook for pasting each month. Every time
the macro runs on the newly pasted in sheet, the formulas in the destination
workbook adjust to the wrong columns. Would a named range work and how would
I use it or name it to use in a linked workbook? Any help here is so
appreciated, thanks,

 

[Barb Reinhardt]

Please post your code.

 

[Karen]

Sub Macro2()
'
' Macro2 Macro
' Kronos report conversion
'
' Keyboard Shortcut: Ctrl+m
'
Cells.Select
Selection.UnMerge
Selection.RowHeight = 12
Selection.ColumnWidth = 12
Columns("E:J").Select
Selection.Insert Shift:=xlToRight
Range("D15").Select
Selection.AutoFill Destination:=Range("D15:K15"), Type:=xlFillDefault
Range("D15:K15").Select
Columns("D").Select
Selection.TextToColumns Destination:=Range("E1:K1"),
DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Columns("D:K").Select
Selection.ColumnWidth = 8
Columns("B").Select
Selection.ColumnWidth = 12
Range("E15").Select
ActiveCell.FormulaR1C1 = "Region"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("F15").Select
ActiveCell.FormulaR1C1 = "Hotel"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("G15").Select
ActiveCell.FormulaR1C1 = "Dept."
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("H15").Select
ActiveCell.FormulaR1C1 = "Department"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("I15").Select
ActiveCell.FormulaR1C1 = "Position"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("J15").Select
ActiveCell.FormulaR1C1 = "Dash"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("K15").Select
ActiveCell.FormulaR1C1 = "Yes/No"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold Italic"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("E15:K15").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
Columns("O:O").Select
Range("O16:O365").Select
Cells.Replace What:=":", Replacement:=".", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.NumberFormat = "0.00"
Range("K4").Select

End With
Range("B1").Select
End Sub