Adding Code at startup 2

[hahnsm]

Can anyone help me with creating code that at startup of an excel file, that will
go to my 'ChartInfo' spreadsheet;
sort the date column (column A);
remove the dates that are more than a year old; and
paste the dates to another spreadsheet called 'OldData'.

When pasting, I don't want the data on the sheet removed so I need the code to find the next blank row and paste the dates there.

Thanks for your help and time in advance!
Sadie

 

[SkipVought]

hahnsm,

Instead of doing all that copy 'n' paste stuff, why not...

1. using menu item Tools/Options/Chart - check plot visible cells only

2. sort your source data

3. filter your source data

4. view the chart results



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hahnsm]

Skip:
My informaiton is being placed on the sheet from another workbook. Will the filter work if there is going to be data written to the sheet on a daily basis?? I guess I am not sure as to how the filter actually works. Can you help me any further??

Thanks!!

 

[SkipVought]

hahnsm,

You need to do some reading up on filters.

In general a filter acts on data in a table. There can be multiple filters applied to a table. Each filter can have one or more criteria. AutoFilter allows for upt to 2 criteria per filter. Each filter is on one column.

Suppose that your table were...

NAME   SCORE
Skip     95
John     88
Mary     99
John     72
Skip     84
Fred     92
John     89
Mary     93

Turn on AutoFilter by
1. selecting any cell within the table
2. Date/Filter/AutoFilter

In the NAME filter select John -- the result is that all rows with NAME other than John are hidden.

You could apply another filter to SCORE to see all scores greater than 85, for instance.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hahnsm]

Been reading up this morning on filters!!
Thank you for your input! It is greatly appreciated!
Sadie

 

[hahnsm]

Skip:
Maybe you could help me out with another problem that I have been trying to work out this morning. I have a spreadsheet that looks like:

DATE NAME QUESTION1 QUESTION2 QUESTION3
10/03 Jay 2 1 3
09/03 Bob 2 2 2
10/03 Jim 1 1 1

I want only the ones to be visible. I tried doing a custom filter but it goes by whole rows. I tried VBA as an if statement:
Columns("I:X&quot.Select
If Cells.Value <> 1 Then
Cells.Hide
Else
Cells.Show
End If
But I receive a memory error for my computer.
Is my code wrong or is there a better way to go about this!
I appreciate all of your help!
Sadie

 

[SkipVought]

Show me what you want it to look like after the process.

I can't understand what you do want!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hahnsm]

Quick response!! Sorry for the misunderstanding (my numbers didn't align correctly in the submit). . .
This is what I have:
DATE NAME QUESTION1 QUESTION2 QUESTION3
10/03 Jay 2 1 3
09/03 Bob 2 2 2
10/03 Jim 1 1 1

This is what I want:
DATE NAME QUESTION1 QUESTION2 QUESTION3
10/03 Jay 1
09/03 Bob
10/03 Jim 1 1 1

I want the 2's and 3's to be deleted. I only want the 1's to show.
Thanks again!!
Sadie

 

[SkipVought]

Here's what I got with a pivot table...

Sum of Value		Question		
DATE   Name  QUESTION1  QUESTION2  QUESTION3
3-Sep  Jay              1	
       Jim              1	
3-Oct  Jim   1                     1

I had to transform your data into a table with headings...

DATE	Name	Question	Value

Then I sorted by Value

Then I selected the range with Values of 1 along with headings

and

proceeded to build a Pivot Table

Took me about 2 minutes to do all that since it's mostly point 'n' click!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hahnsm]

Thanks you so much again!! I should have thought about a pivot table!!
Thanks
Sadie

 

[durrenj]

If you don't want to rearrange the original data, you could also use Format>Conditional Formating to change the cell font color to the background color for all cell values <> 1. That will make all data &quot;invisible&quot; unless values are 1. If data values are typed or paste special values into a preformated range, you will only need to format the data range once and each cell will automatically appear or disappear as the values change.

 

[SkipVought]

durrenj,

Great option!

It could be made more variable by creating a cell with Data/Validation - List and the List has ALL the values for Questions. I named range of the validation cell &quot;fValue&quot; So the Comditional Formatting formula is ...

=C2<>fValue

Then you select a value in the validation drop down list and the display changes!

==> * ;-)


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com