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]

Hi

What is there in column AA that is unique to the data that you would enter where?

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

 

[TenBellie]

The data in AA 1 is a date (AA is only an example it could be ZZ etc), i was going to type the date i need to print in A 1 and hopefully the macro would search and find a match then print of the data in columns A, B & C then AA, AB and AC but next to each other. There could be 70+ rows of data to print depending on the day.

 

[SkipVought]

So what is the logic?

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

 

[TenBellie]

The logic?

Its for a school out of hpurs club. I have to print names attendees for a register to make sure everyomne attends and no child is missing (5-8year olds)

The pupils names are in A and B, class number in C. In the other rows D onward are AM or PM attendees. I have to keep the whole school year for auditing/queries.

just trying to make life easier but not having to hide/unhide all the time someone told me a macro would solve my problem

Type a date in A1 01/02/13. This would then be the same as in say AA1 and print off pupils hames and who was attending.... was just hoping someone could help me...

 

[SkipVought]

Logic example (my guess):

If the dates in row 1 to the right of column C are less than the date entered, then hide those columns. This can result in multiple columns to the right of column C still visible if the dates in row 1 are greater than or equal to the date entered.

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

 

[TenBellie]

Thats what i am doing manually every day - then unhiding and re-hiding etc as i said was just hoping some cleaver person could show me a code that would help make this easy with teh click of a button....

i can record a macro but with columns being diffrent every day it only work the day i record it.

 

[SkipVought]

I'm just trying to understand EXACTLY what you expect to happen

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

 

[TenBellie]

Sorry...

I would like to print columns a,b & c, and also print any other 3 consecutive columns based on a date i manually type into cell A1.

The date match would come from from cell A1 aligning to the date in AA1, or ZZ1, etc.

Tried to show it below - Column A manually type the date in row 1. Other dates are always in so want to print A, b and C also the 3 rows where there is a date match

Column A Column B Column C Column D....... Column AA Colun AB Column AC

Row 1 01/02/13 15/01/13 01/02/13 AM PM

Row 2 John Smith class 1 Yes Yes
Row 3 Brian Smith class 2 Yes Yes
'
'
'
'
Row 100 John Doe class 3 Yes Yes

Hopefully this is clearer... thanks for trying to help

 

[SkipVought]

Your dates in row 1 are disturbing!!!

1) are they randomly arranged, ascending, descending?

2) if you SELECT row 1 and change the NUMBER FORMAT to GENERAL, what happens? (You can REDO to return the original)

Please answer BOTH qusetions fully.

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

 

[TenBellie]

The data is sorted in Surname order - currentlly i have 200 children registered but this is growing

Row 1 Is where the dates of the week are - if i format them they go to numbers. I have them entered as dd/mm/yy

Row 2 is the header row
Christain Name is in cell A 2
Surname is in Cell B 2
Class number is in Cell C 2 .

After column C the header rows are (and these repeat for the whole school year) - which is why i get to AA as my example
Ammount Paid is in cell D2 - eg £5.00
AM is in cell E2 ( I type a Y in here if in blank if not)
PM is in Cell F2 ( I type a Y in here if in blank if not)

At the bottom of the sheet it totals up all the Y's and the cash ammount

Really appreciate this....

 

[SkipVought]

So when you change the number format to GENERAL for the cell containing 15/01/13 what happens EXACTLY?

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

 

[TenBellie]

It changes it to 41289 when i change it back it goes back to the 15/01/13

 

[SkipVought]

Okay, I had to be sure.

You still did not tell me about the order of the dates in row 1. This is important to the process.

Could you copy & paste a few representative rows (3) and columns (10)?

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

 

[TenBellie]

They go in consecutive order (merged cells D, E & F, - G, H & I etc abouve header row saying Ammount Paid - AM - PM)

Wed 05 Sep 12 - Thu 06 Sep 12 - Fri 07 Sep 12 - Mon 10 Sep 12 - Tue 11 Sep 12 - Wed 12 Sep 12

 

[SkipVought]

Right-click the sheet tab and select view code

Paste this code in the code sheet

Sub worksheet_change(target as range)
   Dim rFound as range
   If not 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

Untested from my mobile

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

 

[SkipVought]

Oh yes, when you enter a date in A1, the columns get hidden.

When you DELETE the value in A1, the hidden columns display.

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

 

[TenBellie]

It has a run time error '424' object required

The row "If not intersect(target, [A1]) is nothing then" is in yellow when i press de-bug

 

[SkipVought]

Type application. before intersect like

 If application.intersect(........

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

 

[TenBellie]

Sorry i know i'm a pain and i do really appreciate this but still has an error...

same error code the line is the same place it fails

If Application.Intersect(target, [A1]) Is Nothing Then