I have a spreadsheet that tracks inspection dates and ratings. I have macro
that moves the current dates and ratings to the prior year at the end of the
inspection season. The ratings are picked from a validation list, but the
validation is lost. Is there a way to recreate the validation within the
macro?

Record a macro when you reapply the data|validation rules????

Brian T wrote:
>
> I have a spreadsheet that tracks inspection dates and ratings. I have macro
> that moves the current dates and ratings to the prior year at the end of the
> inspection season. The ratings are picked from a validation list, but the
> validation is lost. Is there a way to recreate the validation within the
> macro?

--

Dave Peterson

Here is the code I have to move the dates and ratings. Recording and
inserting that code didn't work for me.


Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

end sub

"Dave Peterson" wrote:

> Record a macro when you reapply the data|validation rules????
>
> Brian T wrote:
> >
> > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > that moves the current dates and ratings to the prior year at the end of the
> > inspection season. The ratings are picked from a validation list, but the
> > validation is lost. Is there a way to recreate the validation within the
> > macro?
>
> --
>
> Dave Peterson
>

First, I don't see anything that would destroy any data|validation in the
original cells.

Second, I don't see any code where you tried to apply data|validation to
anything.

Copying the cells to a new location shouldn't destroy the data|validation. In
fact, the receiving cell should inherit the same formatting--including
data|validation that the sending cell had.

Maybe the data|validation rules were already lost before the code you posted
started.

And your code (at the the first two for/next loops) isn't really moving
anything--it's copy|pasting.



Brian T wrote:
>
> Here is the code I have to move the dates and ratings. Recording and
> inserting that code didn't work for me.
>
> Sub Move_Date()
>
> 'Moves current inspection date and ratings
> 'to previous inspection date and rating columns
>
> 'Class 1
> Dim NewInsp_1 As Range
> Dim NewRating_1 As Range
> Dim NewSent_1 As Range
> Dim myCell_1a As Range
> Dim myCell_1b As Range
> Dim myCell_1c As Range
>
> Set NewInsp_1 = Range("Current_Date1")
> Set NewRating_1 = Range("Current_Rating1")
> Set NewSent_1 = Range("Current_Report1")
>
> For Each myCell_1a In NewInsp_1.Cells
> If myCell_1a.Value <> "" Then
> myCell_1a.Copy myCell_1a.Offset(, -2)
> myCell_1a.ClearContents
> End If
> Next myCell_1a
>
> For Each myCell_1b In NewRating_1.Cells
> If myCell_1b.Value <> "" Then
> myCell_1b.Copy myCell_1b.Offset(, -3)
> myCell_1b.ClearContents
> End If
> Next myCell_1b
>
> For Each myCell_1c In NewSent_1.Cells
> If myCell_1c.Value <> "" Then
> myCell_1c.ClearContents
> End If
> Next myCell_1c
>
> end sub
>
> "Dave Peterson" wrote:
>
> > Record a macro when you reapply the data|validation rules????
> >
> > Brian T wrote:
> > >
> > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > that moves the current dates and ratings to the prior year at the end of the
> > > inspection season. The ratings are picked from a validation list, but the
> > > validation is lost. Is there a way to recreate the validation within the
> > > macro?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Sorry my bad, please look at this:

"Brian T" wrote:

> Here is the code I have to move the dates and ratings. Recording and
> inserting that code didn't work for me.
>
>
> Sub Move_Date()
>
> 'Moves current inspection date and ratings
> 'to previous inspection date and rating columns
>
> 'Class 1
> Dim NewInsp_1 As Range
> Dim NewRating_1 As Range
> Dim NewSent_1 As Range
> Dim myCell_1a As Range
> Dim myCell_1b As Range
> Dim myCell_1c As Range
>
> Set NewInsp_1 = Range("Current_Date1")
> Set NewRating_1 = Range("Current_Rating1")
> Set NewSent_1 = Range("Current_Report1")
>
> For Each myCell_1a In NewInsp_1.Cells
> If myCell_1a.Value <> "" Then
> myCell_1a.Copy myCell_1a.Offset(, -2)
> myCell_1a.ClearContents
> End If
> Next myCell_1a
>
> For Each myCell_1b In NewRating_1.Cells
> If myCell_1b.Value <> "" Then
> myCell_1b.Copy myCell_1b.Offset(, -3)
> myCell_1b.ClearContents
> End If
> Next myCell_1b
>
> For Each myCell_1c In NewSent_1.Cells
> If myCell_1c.Value <> "" Then
> myCell_1c.ClearContents
> End If
> Next myCell_1c
>
> end sub
>
> "Dave Peterson" wrote:
>
> > Record a macro when you reapply the data|validation rules????
> >
> > Brian T wrote:
> > >
> > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > that moves the current dates and ratings to the prior year at the end of the
> > > inspection season. The ratings are picked from a validation list, but the
> > > validation is lost. Is there a way to recreate the validation within the
> > > macro?
> >
> > --
> >
> > Dave Peterson
> >

Is there a difference between this and your previous post?

Brian T wrote:
>
> Sorry my bad, please look at this:
>
> "Brian T" wrote:
>
> > Here is the code I have to move the dates and ratings. Recording and
> > inserting that code didn't work for me.
> >
> >
> > Sub Move_Date()
> >
> > 'Moves current inspection date and ratings
> > 'to previous inspection date and rating columns
> >
> > 'Class 1
> > Dim NewInsp_1 As Range
> > Dim NewRating_1 As Range
> > Dim NewSent_1 As Range
> > Dim myCell_1a As Range
> > Dim myCell_1b As Range
> > Dim myCell_1c As Range
> >
> > Set NewInsp_1 = Range("Current_Date1")
> > Set NewRating_1 = Range("Current_Rating1")
> > Set NewSent_1 = Range("Current_Report1")
> >
> > For Each myCell_1a In NewInsp_1.Cells
> > If myCell_1a.Value <> "" Then
> > myCell_1a.Copy myCell_1a.Offset(, -2)
> > myCell_1a.ClearContents
> > End If
> > Next myCell_1a
> >
> > For Each myCell_1b In NewRating_1.Cells
> > If myCell_1b.Value <> "" Then
> > myCell_1b.Copy myCell_1b.Offset(, -3)
> > myCell_1b.ClearContents
> > End If
> > Next myCell_1b
> >
> > For Each myCell_1c In NewSent_1.Cells
> > If myCell_1c.Value <> "" Then
> > myCell_1c.ClearContents
> > End If
> > Next myCell_1c
> >
> > end sub
> >
> > "Dave Peterson" wrote:
> >
> > > Record a macro when you reapply the data|validation rules????
> > >
> > > Brian T wrote:
> > > >
> > > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > > that moves the current dates and ratings to the prior year at the end of the
> > > > inspection season. The ratings are picked from a validation list, but the
> > > > validation is lost. Is there a way to recreate the validation within the
> > > > macro?
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Yes there is, i added code at the bottom that is supposed to re-create the
validation drop down list.

"Dave Peterson" wrote:

> Is there a difference between this and your previous post?
>
> Brian T wrote:
> >
> > Sorry my bad, please look at this:
> >
> > "Brian T" wrote:
> >
> > > Here is the code I have to move the dates and ratings. Recording and
> > > inserting that code didn't work for me.
> > >
> > >
> > > Sub Move_Date()
> > >
> > > 'Moves current inspection date and ratings
> > > 'to previous inspection date and rating columns
> > >
> > > 'Class 1
> > > Dim NewInsp_1 As Range
> > > Dim NewRating_1 As Range
> > > Dim NewSent_1 As Range
> > > Dim myCell_1a As Range
> > > Dim myCell_1b As Range
> > > Dim myCell_1c As Range
> > >
> > > Set NewInsp_1 = Range("Current_Date1")
> > > Set NewRating_1 = Range("Current_Rating1")
> > > Set NewSent_1 = Range("Current_Report1")
> > >
> > > For Each myCell_1a In NewInsp_1.Cells
> > > If myCell_1a.Value <> "" Then
> > > myCell_1a.Copy myCell_1a.Offset(, -2)
> > > myCell_1a.ClearContents
> > > End If
> > > Next myCell_1a
> > >
> > > For Each myCell_1b In NewRating_1.Cells
> > > If myCell_1b.Value <> "" Then
> > > myCell_1b.Copy myCell_1b.Offset(, -3)
> > > myCell_1b.ClearContents
> > > End If
> > > Next myCell_1b
> > >
> > > For Each myCell_1c In NewSent_1.Cells
> > > If myCell_1c.Value <> "" Then
> > > myCell_1c.ClearContents
> > > End If
> > > Next myCell_1c
> > >
> > > end sub
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Record a macro when you reapply the data|validation rules????
> > > >
> > > > Brian T wrote:
> > > > >
> > > > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > > > that moves the current dates and ratings to the prior year at the end of the
> > > > > inspection season. The ratings are picked from a validation list, but the
> > > > > validation is lost. Is there a way to recreate the validation within the
> > > > > macro?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
>
> --
>
> Dave Peterson
>

My bad again,

Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim NewType_1 As Range
Dim NewAgency_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")
Set NewType_1 = Range("Type1")
Set NewAgency_1 = Range("Inspector")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewType_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewAgency_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

'Removes vailidation when moving new dates

Range("I:I").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

'reset drop down lists
Range("Current_Rating1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Condition_Rating"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub



"Dave Peterson" wrote:

> Is there a difference between this and your previous post?
>
> Brian T wrote:
> >
> > Sorry my bad, please look at this:
> >
> > "Brian T" wrote:
> >
> > > Here is the code I have to move the dates and ratings. Recording and
> > > inserting that code didn't work for me.
> > >
> > >
> > > Sub Move_Date()
> > >
> > > 'Moves current inspection date and ratings
> > > 'to previous inspection date and rating columns
> > >
> > > 'Class 1
> > > Dim NewInsp_1 As Range
> > > Dim NewRating_1 As Range
> > > Dim NewSent_1 As Range
> > > Dim myCell_1a As Range
> > > Dim myCell_1b As Range
> > > Dim myCell_1c As Range
> > >
> > > Set NewInsp_1 = Range("Current_Date1")
> > > Set NewRating_1 = Range("Current_Rating1")
> > > Set NewSent_1 = Range("Current_Report1")
> > >
> > > For Each myCell_1a In NewInsp_1.Cells
> > > If myCell_1a.Value <> "" Then
> > > myCell_1a.Copy myCell_1a.Offset(, -2)
> > > myCell_1a.ClearContents
> > > End If
> > > Next myCell_1a
> > >
> > > For Each myCell_1b In NewRating_1.Cells
> > > If myCell_1b.Value <> "" Then
> > > myCell_1b.Copy myCell_1b.Offset(, -3)
> > > myCell_1b.ClearContents
> > > End If
> > > Next myCell_1b
> > >
> > > For Each myCell_1c In NewSent_1.Cells
> > > If myCell_1c.Value <> "" Then
> > > myCell_1c.ClearContents
> > > End If
> > > Next myCell_1c
> > >
> > > end sub
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Record a macro when you reapply the data|validation rules????
> > > >
> > > > Brian T wrote:
> > > > >
> > > > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > > > that moves the current dates and ratings to the prior year at the end of the
> > > > > inspection season. The ratings are picked from a validation list, but the
> > > > > validation is lost. Is there a way to recreate the validation within the
> > > > > macro?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
>
> --
>
> Dave Peterson
>

I still don't see anything in the original code that would remove the
conditional formatting.

And I don't see a problem with the code that adds it back--but I didn't test
it. I have no idea what each of those ranges refer to.

Brian T wrote:
>
> My bad again,
>
> Sub Move_Date()
>
> 'Moves current inspection date and ratings
> 'to previous inspection date and rating columns
>
> 'Class 1
> Dim NewInsp_1 As Range
> Dim NewRating_1 As Range
> Dim NewSent_1 As Range
> Dim NewType_1 As Range
> Dim NewAgency_1 As Range
> Dim myCell_1a As Range
> Dim myCell_1b As Range
> Dim myCell_1c As Range
>
> Set NewInsp_1 = Range("Current_Date1")
> Set NewRating_1 = Range("Current_Rating1")
> Set NewSent_1 = Range("Current_Report1")
> Set NewType_1 = Range("Type1")
> Set NewAgency_1 = Range("Inspector")
>
> For Each myCell_1a In NewInsp_1.Cells
> If myCell_1a.Value <> "" Then
> myCell_1a.Copy myCell_1a.Offset(, -2)
> myCell_1a.ClearContents
> End If
> Next myCell_1a
>
> For Each myCell_1b In NewRating_1.Cells
> If myCell_1b.Value <> "" Then
> myCell_1b.Copy myCell_1b.Offset(, -3)
> myCell_1b.ClearContents
> End If
> Next myCell_1b
>
> For Each myCell_1c In NewSent_1.Cells
> If myCell_1c.Value <> "" Then
> myCell_1c.ClearContents
> End If
> Next myCell_1c
>
> For Each myCell_1c In NewType_1.Cells
> If myCell_1c.Value <> "" Then
> myCell_1c.ClearContents
> End If
> Next myCell_1c
>
> For Each myCell_1c In NewAgency_1.Cells
> If myCell_1c.Value <> "" Then
> myCell_1c.ClearContents
> End If
> Next myCell_1c
>
> 'Removes vailidation when moving new dates
>
> Range("I:I").Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
> Operator _
> :=xlBetween
> .IgnoreBlank = True
> .InCellDropdown = True
> .ShowInput = True
> .ShowError = True
> End With
>
> 'reset drop down lists
> Range("Current_Rating1").Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="Condition_Rating"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> End Sub
>
> "Dave Peterson" wrote:
>
> > Is there a difference between this and your previous post?
> >
> > Brian T wrote:
> > >
> > > Sorry my bad, please look at this:
> > >
> > > "Brian T" wrote:
> > >
> > > > Here is the code I have to move the dates and ratings. Recording and
> > > > inserting that code didn't work for me.
> > > >
> > > >
> > > > Sub Move_Date()
> > > >
> > > > 'Moves current inspection date and ratings
> > > > 'to previous inspection date and rating columns
> > > >
> > > > 'Class 1
> > > > Dim NewInsp_1 As Range
> > > > Dim NewRating_1 As Range
> > > > Dim NewSent_1 As Range
> > > > Dim myCell_1a As Range
> > > > Dim myCell_1b As Range
> > > > Dim myCell_1c As Range
> > > >
> > > > Set NewInsp_1 = Range("Current_Date1")
> > > > Set NewRating_1 = Range("Current_Rating1")
> > > > Set NewSent_1 = Range("Current_Report1")
> > > >
> > > > For Each myCell_1a In NewInsp_1.Cells
> > > > If myCell_1a.Value <> "" Then
> > > > myCell_1a.Copy myCell_1a.Offset(, -2)
> > > > myCell_1a.ClearContents
> > > > End If
> > > > Next myCell_1a
> > > >
> > > > For Each myCell_1b In NewRating_1.Cells
> > > > If myCell_1b.Value <> "" Then
> > > > myCell_1b.Copy myCell_1b.Offset(, -3)
> > > > myCell_1b.ClearContents
> > > > End If
> > > > Next myCell_1b
> > > >
> > > > For Each myCell_1c In NewSent_1.Cells
> > > > If myCell_1c.Value <> "" Then
> > > > myCell_1c.ClearContents
> > > > End If
> > > > Next myCell_1c
> > > >
> > > > end sub
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Record a macro when you reapply the data|validation rules????
> > > > >
> > > > > Brian T wrote:
> > > > > >
> > > > > > I have a spreadsheet that tracks inspection dates and ratings. I have macro
> > > > > > that moves the current dates and ratings to the prior year at the end of the
> > > > > > inspection season. The ratings are picked from a validation list, but the
> > > > > > validation is lost. Is there a way to recreate the validation within the
> > > > > > macro?
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson