|
Macro Problem
Hello,
I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
That depends on what the macro does, post the code
"Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
Here it is:
’ Go to the date columns and change all the formats to *3/14/2001 ’ Click junction between A and 1 ’ Click between column A and column B to fit column widths ’ Insert column at B ’ Go to B1 and name it NSC # Check ’ Go to B2 and type =Len(Trim(A2)) ’ Go to Format, select conditional formatting, select cell value is greater than 10, format pattern as red ’ Copy / paste B2 to row 15,000 ’ Insert column at D ’ Go to D1 and type EIN# Check ’ Go to D2 and type = Len(Trim(C2)) ’ Go to Format, select conditional formatting, select cell value is greater than 9, format pattern as red ’ Copy / paste D2 to row 15,000 ’ Insert column at F ’ Go to F1 and type NPI# Check ’ Go to F2 and type = Len(Trim(E2)) ’ Go to Format, select conditional formatting, select cell value is greater than 10, format pattern as red ’ Copy / paste F2 to row 15,000 ’ Insert Column at H ’ Go to H1 and type Legal Business Name for Blanks ’ Go to H2 and type =g2= ¦this checks for blanks ’ Go to format, select conditional formatting, select formula is equal to =g2=, format pattern as red ’ Copy / paste H2 to row 15,000 ’ Insert column at J ’ Go to J1 and type Check Site Name for Blanks ’ Go to J2 and type =i2=¦this checks for blanks ’ Go to format, select conditional formatting, select formula is equal to =i2=, format pattern as red ’ Copy / paste J2 to row 15,000 ’ Insert column at L ’ Go to L1 and type Check Physical Address for Blanks ’ Go to L2 and type =k2=¦this checks for blanks ’ Go to format, select conditional formatting, select formula is equal to =k2=, format pattern as red ’ Copy / paste L2 to row 15,000 ’ Insert column at N ’ Go to NI and type Check if City Has Blanks ’ Go to N2 and type =m2=¦this check for blanks ’ Go to format, select conditional formatting, formula is equal to =m2=, format pattern as red ’ Copy / paste to row 15000 ’ Insert column at P ’ Go to P1 and type Check if State is Blank ’ Resize column P ’ Go to P2 and type =o2=¦this checks for blanks ’ Go to format, select conditional formatting, formula is equal to =o2= ’ Insert column at R ’ Go to R1 and type Check if Zip Greater than 5 Characters ’ Resize column R ’ Go to R2 and type =Len(Q2) ’ Go to format, select conditional formatting, cell value is greater than 5, format pattern as red ’ Copy / paste to row 15000 ’ Insert column at W ’ Go to W1 and type Check Date Progression ’ Go to W2 and type =And(V2U2,U2T2,T2S2)¦this checks to make sure dates occur in logical sequence ’ Format W2 as general ’ Go to format, select conditional formatting, select cell value is equal to FALSE, format pattern as red ’ Copy / paste to row 15000 ’ Stop recording ’ Delete uneccessary rows below last row of real data "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
Sorry, here is the actual code:
Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
As with most recorded macros its a bit wordy and could be cleaned up a lot
but what are you expecting it to do other than insert formats and formulas, there's nothing else in it Mike "Daren" wrote: Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
It's only inserting the formulas and formats and not executing the macro.
That's the problem. It worked on one file when I was recording it but not the others. "Mike H" wrote: As with most recorded macros its a bit wordy and could be cleaned up a lot but what are you expecting it to do other than insert formats and formulas, there's nothing else in it Mike "Daren" wrote: Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
The macro only executed in the file where I created it. It didn't execute in
the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
If you have the desired file and sheet active and fire the macro from the
original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
I created it in Personal Workbook. I executed it using personal workbook
into a different file and that didn't work. Am I missing something here in trying to put it in a template somewhere and then running it? "Don Guillett" wrote: If you have the desired file and sheet active and fire the macro from the original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
Again, tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... I created it in Personal Workbook. I executed it using personal workbook into a different file and that didn't work. Am I missing something here in trying to put it in a template somewhere and then running it? "Don Guillett" wrote: If you have the desired file and sheet active and fire the macro from the original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
That's what I was doing before but it would work in a different file.
"Don Guillett" wrote: Again, tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... I created it in Personal Workbook. I executed it using personal workbook into a different file and that didn't work. Am I missing something here in trying to put it in a template somewhere and then running it? "Don Guillett" wrote: If you have the desired file and sheet active and fire the macro from the original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
Did you send ALL of the macro? There was no sub dothis() line suggesting
there is more code? Select the file where you want the macro to runfrom that file tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... That's what I was doing before but it would work in a different file. "Don Guillett" wrote: Again, tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... I created it in Personal Workbook. I executed it using personal workbook into a different file and that didn't work. Am I missing something here in trying to put it in a template somewhere and then running it? "Don Guillett" wrote: If you have the desired file and sheet active and fire the macro from the original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: Hello, I recorded a macro and noticed while recording that Excel was autosaving. When I ran the macro in another file, all it did was put in the formulas and formats where they were supposed to be. What might be the issue? Thanks. |
Macro Problem
Yes, I sent all of the code. By your question, do you mean that the code
might not execute properly? "Don Guillett" wrote: Did you send ALL of the macro? There was no sub dothis() line suggesting there is more code? Select the file where you want the macro to runfrom that file tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... That's what I was doing before but it would work in a different file. "Don Guillett" wrote: Again, tools macromacrospersonal.xlsselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... I created it in Personal Workbook. I executed it using personal workbook into a different file and that didn't work. Am I missing something here in trying to put it in a template somewhere and then running it? "Don Guillett" wrote: If you have the desired file and sheet active and fire the macro from the original macro module by selecting and touching f5 it will execute in the desired sheet. Or, from the menu tools macromacrosall open workbooksselect itrun -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... The macro only executed in the file where I created it. It didn't execute in the others. Where can I make a template sheet? Would I be able to copy from the template sheet into the editor of another file? "Don Guillett" wrote: Other than cleaning up the code what do you want? It seems that making a template sheet might be better?? Sub ddd() Columns("J:P").NumberFormat = "m/d/yyyy" Columns("B").Insert Range("B1") = "NSC# Check" Range("B2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("B2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("B3:B15000") End With Range("B2").Copy Range("B3:B15000") Columns("D").Insert Shift:=xlToRight Range("D1") = "EIN# Check" Range("D2").FormulaR1C1 = "=LEN(TRIM(RC[-1]))" With Range("D2") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" .FormatConditions(1).Interior.ColorIndex = 3 .Copy Range("D3:D15000") End With 'etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Daren" wrote in message ... Sorry, here is the actual code: Columns("J:P").Select Selection.NumberFormat = "m/d/yyyy" Cells.Select Cells.EntireColumn.AutoFit Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "NSC# Check" Range("B2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("B2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("B3:B15000").Select Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("B3:B15000").Select ActiveSheet.Paste Columns("D:D").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "EIN# Check" Range("D2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("D2").Select Selection.Copy Range("D2").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="9" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("D3:D15000").Select ActiveSheet.Paste Columns("F:F").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("F1").Select ActiveCell.FormulaR1C1 = "NPI# Check" Range("F2").Select ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-1]))" Range("F2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="10" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("F3:F15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("H:H").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Check Legal Business Name for Blanks" Range("H2").Select Columns("H:H").ColumnWidth = 49.43 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("H2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("H3:H15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=1 Columns("J:J").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("J1").Select ActiveCell.FormulaR1C1 = "Check Site Name for Blanks" Range("J2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("J2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("J3:J15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=2 Columns("L:L").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("L1").Select ActiveCell.FormulaR1C1 = "Check Physical Address for Blanks" Range("L2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("L2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("L3:L15000").Select ActiveSheet.Paste Columns("N:N").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("N1").Select ActiveCell.FormulaR1C1 = "Check if City Has Blanks" Range("N2").Select ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("N2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 ActiveWindow.SmallScroll ToRight:=1 Selection.Copy Range("N3:N15000").Select ActiveSheet.Paste Columns("P:P").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("P1").Select ActiveCell.FormulaR1C1 = "Check if State is Blank" Range("P2").Select Columns("P:P").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=""""" Range("P2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=O2=""""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("P3:P15000").Select ActiveSheet.Paste Columns("R:R").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("R1").Select ActiveCell.FormulaR1C1 = "Check if Zip Greater than 5 Characters" Range("R2").Select Columns("R:R").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("R2").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="5" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("R3:R15000").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=5 Columns("W:W").Select Application.CutCopyMode = False Selection.Insert Shift:=xlToRight Range("W1").Select ActiveCell.FormulaR1C1 = "Check Date Progression" Range("W2").Select ActiveCell.FormulaR1C1 = "=AND(RC[-1]RC[-2],RC[-2]RC[-3],RC[-3]RC[-4])" Range("W2").Select Selection.NumberFormat = "General" Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="FALSE" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.Copy Range("W3:W15000").Select ActiveSheet.Paste Range("W2").Select End Sub Thanks! "Mike H" wrote: That depends on what the macro does, post the code "Daren" wrote: |
| All times are GMT +1. The time now is 12:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com