Macro Problem

D

Daren

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.
 
D

Daren

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(V2>U2,U2>T2,T2>S2)…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
 
D

Daren

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!
 
D

Don Guillett

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
[email protected]
Daren said:
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 said:
That depends on what the macro does, post the code
Click to expand...
Click to expand...
 
M

Mike H

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 said:
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 said:
That depends on what the macro does, post the code
Click to expand...
Click to expand...
 
D

Daren

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 said:
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 said:
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 said:
That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Daren

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 said:
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
[email protected]
Daren said:
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 said:
That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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 said:
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
[email protected]
Daren said:
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!


:

That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Daren

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 said:
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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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 said:
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
[email protected]
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!


:

That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Again,
tools> macro>macros>personal.xls>select it>run
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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 said:
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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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?

:

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
[email protected]
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!


:

That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Daren

That's what I was doing before but it would work in a different file.

Don Guillett said:
Again,
tools> macro>macros>personal.xls>select it>run
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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 said:
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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?

:

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
[email protected]
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!


:

That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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 run>from that file
tools> macro>macros>personal.xls>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
That's what I was doing before but it would work in a different file.

Don Guillett said:
Again,
tools> macro>macros>personal.xls>select it>run
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
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?

:

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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?

:

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
[email protected]
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!


:

That depends on what the macro does, post the code

:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Daren

Yes, I sent all of the code. By your question, do you mean that the code
might not execute properly?

Don Guillett said:
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 run>from that file
tools> macro>macros>personal.xls>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Daren said:
That's what I was doing before but it would work in a different file.

Don Guillett said:
Again,
tools> macro>macros>personal.xls>select it>run
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?

:

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> macro>macros>all open workbooks>select it>run

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?

:

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
[email protected]
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!


:

That depends on what the macro does, post the code

:
Click to expand...
Click to expand...
Click to expand...
 
Top