Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro changes formulas on destination worksheet
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, -- Excelsolutions4U |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro changes formulas on destination worksheet
Please post your code.
-- HTH, Barb Reinhardt "Karen" wrote: 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, -- Excelsolutions4U |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro changes formulas on destination worksheet
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: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 -- Excelsolutions4U "Barb Reinhardt" wrote: Please post your code. -- HTH, Barb Reinhardt "Karen" wrote: 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, -- Excelsolutions4U |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink formula with destination in same worksheet | Excel Worksheet Functions | |||
formulas do not copy to destination cells, but as existing totals | Excel Worksheet Functions | |||
Paste in "match destination format" macro code | Excel Discussion (Misc queries) | |||
Display 2 formulas from source workbooks to destination workbooks | Excel Discussion (Misc queries) | |||
sort source workbook data, maintain formulas in destination workb. | Excel Worksheet Functions |