Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding Code at startup 2

Status
Not open for further replies.

hahnsm

MIS
Jun 17, 2003
62
US
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
 
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
 
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!!
 
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...
Code:
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
 
Been reading up this morning on filters!!
Thank you for your input! It is greatly appreciated!
Sadie
 
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").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
 
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
 
Here's what I got with a pivot table...
Code:
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...
Code:
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
 
Thanks you so much again!! I should have thought about a pivot table!!
Thanks
Sadie
 
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.
 
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 ...
Code:
=C2<>fValue
Then you select a value in the validation drop down list and the display changes!

==> * ;-)


Skip,
Skip@TheOfficeExperts.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top