Hi,

Have Worksheet = Info Contains Columns A to E

Have Worksheet Called Selected

Have Worksheet Call Choose

That I would like to do is:-

Change a1 on Choose to = "2"

Then Display on Selected Colums A to E from info where Column A = Choose A1

No of colums may very ie 1 = 3 rows, 2 = 10 rows etc

Hope you can help

Trev

Trever,

Get rid of sheet "Selected", get rid of sheet "Choose", and use Data filters on sheet "Info".
Select the table, use Data / Filter / Autofilter, then select your 1 or 2 or whatever from the
dropdown at the top of column A on "Info" That's the best way to do what you want.

HTH,
Bernie
MS Excel MVP


"Trever B" wrote in message
...
Hi,

Have Worksheet = Info Contains Columns A to E

Have Worksheet Called Selected

Have Worksheet Call Choose

That I would like to do is:-

Change a1 on Choose to = "2"

Then Display on Selected Colums A to E from info where Column A = Choose A1

No of colums may very ie 1 = 3 rows, 2 = 10 rows etc

Hope you can help

Trev

Thanks Bernie,

Have already been there. Not what I need to do. This is a 40 worksheet
document with graphs. Then automatically export to powerpoint with 40 shops
so automation has to come with it.

What you are suggesting is manual.

Thanks Anyway.

Perhaps someone els can help.

Trev

"Bernie Deitrick" wrote:

Trever,

Get rid of sheet "Selected", get rid of sheet "Choose", and use Data filters on sheet "Info".
Select the table, use Data / Filter / Autofilter, then select your 1 or 2 or whatever from the
dropdown at the top of column A on "Info" That's the best way to do what you want.

HTH,
Bernie
MS Excel MVP


"Trever B" wrote in message
...
Hi,

Have Worksheet = Info Contains Columns A to E

Have Worksheet Called Selected

Have Worksheet Call Choose

That I would like to do is:-

Change a1 on Choose to = "2"

Then Display on Selected Colums A to E from info where Column A = Choose A1

No of colums may very ie 1 = 3 rows, 2 = 10 rows etc

Hope you can help

Trev

Trev,

Well, I won't comment on your application design, but will help you further along down your chosen
path.

You can use a worksheet change event, for sheet "Choose" - Copy the code below, right-click the
sheet tab of sheet "Choose", select "View Code" and paste the code into the window that appears. It
will do what you had described in your initial post.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$A$1" Then Exit Sub

Application.EnableEvents = False

Worksheets("Selected").Range("A:E").ClearContents
With Worksheets("Info").Range("A1").CurrentRegion
.AutoFilter field:=1, Criteria1:=CStr(Target.Value)
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected").Cells(1, 1)
.AutoFilter
End With
Application.EnableEvents = True

End Sub


"Trever B" wrote in message
...
Thanks Bernie,

Have already been there. Not what I need to do. This is a 40 worksheet
document with graphs. Then automatically export to powerpoint with 40 shops
so automation has to come with it.

What you are suggesting is manual.

Thanks Anyway.

Perhaps someone els can help.

Trev

"Bernie Deitrick" wrote:

Trever,

Get rid of sheet "Selected", get rid of sheet "Choose", and use Data filters on sheet "Info".
Select the table, use Data / Filter / Autofilter, then select your 1 or 2 or whatever from the
dropdown at the top of column A on "Info" That's the best way to do what you want.

HTH,
Bernie
MS Excel MVP


"Trever B" wrote in message
...
Hi,

Have Worksheet = Info Contains Columns A to E

Have Worksheet Called Selected

Have Worksheet Call Choose

That I would like to do is:-

Change a1 on Choose to = "2"

Then Display on Selected Colums A to E from info where Column A = Choose A1

No of colums may very ie 1 = 3 rows, 2 = 10 rows etc

Hope you can help

Trev

Thanks Bernie,

Worked a treat.

Trev

"Bernie Deitrick" wrote:

Trev,

Well, I won't comment on your application design, but will help you further along down your chosen
path.

You can use a worksheet change event, for sheet "Choose" - Copy the code below, right-click the
sheet tab of sheet "Choose", select "View Code" and paste the code into the window that appears. It
will do what you had described in your initial post.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$A$1" Then Exit Sub

Application.EnableEvents = False

Worksheets("Selected").Range("A:E").ClearContents
With Worksheets("Info").Range("A1").CurrentRegion
.AutoFilter field:=1, Criteria1:=CStr(Target.Value)
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Selected").Cells(1, 1)
.AutoFilter
End With
Application.EnableEvents = True

End Sub


"Trever B" wrote in message
...
Thanks Bernie,

Have already been there. Not what I need to do. This is a 40 worksheet
document with graphs. Then automatically export to powerpoint with 40 shops
so automation has to come with it.

What you are suggesting is manual.

Thanks Anyway.

Perhaps someone els can help.

Trev

"Bernie Deitrick" wrote:

Trever,

Get rid of sheet "Selected", get rid of sheet "Choose", and use Data filters on sheet "Info".
Select the table, use Data / Filter / Autofilter, then select your 1 or 2 or whatever from the
dropdown at the top of column A on "Info" That's the best way to do what you want.

HTH,
Bernie
MS Excel MVP


"Trever B" wrote in message
...
Hi,

Have Worksheet = Info Contains Columns A to E

Have Worksheet Called Selected

Have Worksheet Call Choose

That I would like to do is:-

Change a1 on Choose to = "2"

Then Display on Selected Colums A to E from info where Column A = Choose A1

No of colums may very ie 1 = 3 rows, 2 = 10 rows etc

Hope you can help

Trev