advanced filter

[deedee88]

Hi,

I was wondering if anyone can help me. I have 2 worksheets. The first worksheet is a list of various departments and below each department is a formula subtotal.

On the second spreadsheet I used the advanced filter to extract the range for each department.

Question: The copy data only has values. Is there a way to show the formulas?

Any ideas!

Thanks in advance!

 

[Robert E Lee]

Hi

The key to the left of 1/! is sometimes referred to as "pipe"

Ctrl+"pipe" will toggle between formulae and values. Filtering of columns in the formula format will show values in the selection drop-down so I imagine advanced filtering will still work

HTH

Robert

 

[deedee88]

Thanks for your reply, but what I am looking for is the formula to copy from the original spreadsheet to the filter copy spreadsheet.

I think there is no way of getting around this, I will just have to add the formulas manually.

 

[Zack Barresse]

I'm afraid not, unless you used code, or added them manually.

@Robert: It's generally called a tilde. The pipe is another character altogehter and is generally on the same key as the backslash (english 104-key keyboards).

HTH

 

[deedee88]

Hi,

Would you mind helping me with a code to sum add the filter data. Just to let you know I am new to macro and VBA.

Here is my current macro to filter the data.

Sub Macro7()
'
' Macro7 Macro
'

'
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Selection.ClearFormats
Range("A2").Select
Sheets("09 Actual-Forecast").Rows("7:44").AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A7:T7"), Unique:= _
False
End Sub

After the filter data is copied, the total values in the last row (starting from column C and ending in column O) needs to be erased, and also the values in column O to the last row of data needs to be erased and replaced with sum formulas.

I hope this is clear!

Thank you!

 

[Zack Barresse]

Can you describe a little more? We're not sure what the Selection is, so if you can describe what those ranges are that would be great. Also, what sheet did you start on there? Also, what is the SUM formula? Summing what?