Validation list problem

[Greg Snidow]

Greetings all. I have a workbook with a sheet for each month of the year,
and a blank template at the end to add new months. In some of the columns,
the cells are populated by using a pick list which I created using
Data>Validation, and typing the values way below the records. No problems
here. The problem is that if I have to cut and insert a row from one sheet
to the next, the list references in the cut row are off. I tried creating a
sheet called 'Data', and make the list reference 'Data!$A2:A...', but the
error says that I must use a list on the same page. Is there a way for me to
reference a list on another sheet? Or, how could I use VB to programatically
set the list to the range of the list on the new sheet?

 

[Greg Snidow]

Well, I was in a pinch, so I solved my problem by inserting code in the macro
to move the lists back to their original position after every insert. I
would still like to know how to programatically set the list reference.
Thanks

Sub MoveLists()

Dim TopRow As String
Dim LstRow As String

LstRow = [F65000].End(xlUp).Row
Range("F" & LstRow).Activate
Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
ActiveCell.Offset(-1, 0).Activate
End If
Loop

TopRow = ActiveCell.Offset(1, 0).Row

Rows(TopRow & ":" & LstRow).Cut
Rows("200").Select
Selection.Insert shift:=xlDown

End Sub

 

[Greg Snidow]

Well, that plan did not work out so well. I found that regardless of whether
or not the lists are at the same row on each sheet, when I cut the row and
insert it into a new sheet, the reference in Data>validatation source box is
gone. Is there a way to make it stay. The list for the first box is at
F200. Is there a programatic way I can put in the macro to set the source
for the list? I am at my wits end with this. Thank you.

Well, I was in a pinch, so I solved my problem by inserting code in the macro
to move the lists back to their original position after every insert. I
would still like to know how to programatically set the list reference.
Thanks

Sub MoveLists()

Dim TopRow As String
Dim LstRow As String

LstRow = [F65000].End(xlUp).Row
Range("F" & LstRow).Activate
Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
ActiveCell.Offset(-1, 0).Activate
End If
Loop

TopRow = ActiveCell.Offset(1, 0).Row

Rows(TopRow & ":" & LstRow).Cut
Rows("200").Select
Selection.Insert shift:=xlDown

End Sub

Greetings all. I have a workbook with a sheet for each month of the year,
and a blank template at the end to add new months. In some of the columns,
the cells are populated by using a pick list which I created using
Data>Validation, and typing the values way below the records. No problems
here. The problem is that if I have to cut and insert a row from one sheet
to the next, the list references in the cut row are off. I tried creating a
sheet called 'Data', and make the list reference 'Data!$A2:A...', but the
error says that I must use a list on the same page. Is there a way for me to
reference a list on another sheet? Or, how could I use VB to programatically
set the list to the range of the list on the new sheet?

 

[Debra Dalgleish]

You can refer to a list on a different sheet if you name the range that
contains the list. There are instructions here:

http://www.contextures.com/xlDataVal01.html

Well, that plan did not work out so well. I found that regardless of whether
or not the lists are at the same row on each sheet, when I cut the row and
insert it into a new sheet, the reference in Data>validatation source box is
gone. Is there a way to make it stay. The list for the first box is at
F200. Is there a programatic way I can put in the macro to set the source
for the list? I am at my wits end with this. Thank you.

:

Well, I was in a pinch, so I solved my problem by inserting code in the macro
to move the lists back to their original position after every insert. I
would still like to know how to programatically set the list reference.
Thanks

Sub MoveLists()

Dim TopRow As String
Dim LstRow As String

LstRow = [F65000].End(xlUp).Row
Range("F" & LstRow).Activate
Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
ActiveCell.Offset(-1, 0).Activate
End If
Loop

TopRow = ActiveCell.Offset(1, 0).Row

Rows(TopRow & ":" & LstRow).Cut
Rows("200").Select
Selection.Insert shift:=xlDown

End Sub

:

Greetings all. I have a workbook with a sheet for each month of the year,
and a blank template at the end to add new months. In some of the columns,
the cells are populated by using a pick list which I created using
Data>Validation, and typing the values way below the records. No problems
here. The problem is that if I have to cut and insert a row from one sheet
to the next, the list references in the cut row are off. I tried creating a
sheet called 'Data', and make the list reference 'Data!$A2:A...', but the
error says that I must use a list on the same page. Is there a way for me to
reference a list on another sheet? Or, how could I use VB to programatically
set the list to the range of the list on the new sheet?