Excel macro to print columns based on a date in a specific cell 1

[TenBellie]

Hi,
I have a spreadsheet that has to columns of data in A and B, then moving accross there are columns for students attending a club i was wondering if there is a VBA code that would only print the data in Columns A & B and say Column AA if the date matches a day i could type into a cell so they are on the printed paper.

The only way i can do this at the moment is to keep hiding columns C, D, E etc then unhiding when there is a query.

 

[SkipVought]

okay, I am back on my laptop

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rFound As Range
   If Not Application.Intersect(Target, [A1]) Is Nothing Then
       Cells.EntireColumn.Hidden = False
       If Trim(Target.Value) = "" Then Exit Sub
       Set rFound = Rows(1).Find([A1])
       If Not rFound Is Nothing Then
          Range(Cells(1, 4), Cells(1, rFound.Column - 1)).EntireColumn.Hidden = True
       End If
   End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

That works as I tested it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TenBellie]

I am placing the code in an active X command button, the old code i placed under a Form Controls command button.

I cant fine the new code under the other form option hence the active X.

However i now get a new error says = Ambiguous name detected: worksheet_change

 

[SkipVought]

You said nothing about controls or existing code!

Your requirement was for a date entered in A1!

The code works as designed for the given requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TenBellie]

I am not adding it to any other code i cant get it to run unless its under a user button. There is no other macros in there

 

[TenBellie]

Hi = thanks fopr you help i have worked out how to run it...

Public Sub Column_Hide()
Worksheet_Change ([a1])
End Sub

Public Sub Worksheet_Change(ByVal Target As Range)
Dim rFound As Range
Cells.EntireColumn.Hidden = False
If Not Application.Intersect(Target, [a1]) Is Nothing Then
Cells.EntireColumn.Hidden = False
If Trim(Target.Value) = "" Then Exit Sub
Set rFound = Rows(1).Find([a1])
If Not rFound Is Nothing Then
Range(Cells(1, 4), Cells(1, rFound.Column - 1)).EntireColumn.Hidden = True
End If
End If
End Sub

Works a treat thanks you so much

 

[SkipVought]

All the trouble you had was because you did not follow me instructions, I deduce.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]