macro help

[bflan0524]

ive never built a macro so was looking for some help or a resource somebody could point me to.
basically i have a dynamic page that changes based on the person picked on a control page. on this dynamic page there are many columns but two titled $ Change and % Change. i need a filter for this page where you can press the macro and it will sort the page for $ Change from highest to lowest, it could also do the same if you select the % Change column

you can't filter/sort the page currently because of all the formulas on it, it just never works and sorts properly

ty

 

[Logit]

.
Suggestion ... if the formulas create an issue when sorting ...

Copy the sheet data - VALUES ONLY - to a clean sheet. Then do the sorting there.

You can use the MACRO RECORDER to record your actions as you perform the COPY / PASTE / SORT.

Then all you need do is attach a command button to the 'sorting' sheet that is connected to the newly
created macro.


You can also search for COPY PASTE VALUES TO OTHER SHEET. Plenty of examples on the internet and this and other forums.

Then search for SORT COLUMN ASCENDING (or DESCENDING). Again plenty of examples.


Put something together and if you run into a problem ... post your code and advise if you are receiving an error message or
if the code is doing something other than intended.

 

[bflan0524]

.
Suggestion ... if the formulas create an issue when sorting ...

Copy the sheet data - VALUES ONLY - to a clean sheet. Then do the sorting there.

You can use the MACRO RECORDER to record your actions as you perform the COPY / PASTE / SORT.

Then all you need do is attach a command button to the 'sorting' sheet that is connected to the newly
created macro.


You can also search for COPY PASTE VALUES TO OTHER SHEET. Plenty of examples on the internet and this and other forums.

Then search for SORT COLUMN ASCENDING (or DESCENDING). Again plenty of examples.


Put something together and if you run into a problem ... post your code and advise if you are receiving an error message or
if the code is doing something other than intended.

thanks for the response, obviously im ignorant to macros but if i did the above wouldnt the macro then performing the activity on the sheet with the pasted values and not necessarily the original sheet

 

[Logit]

.
I understood you wanted to sort the data ... not continue to have the formulas perform their action.

By copying the VALUES only, you will be copying the values that are present at the time of copying.
Those values will be transferred to the new sheet allowing you to then sort those values. The formulas
will not be copied to the new sheet so no further changes to the values on the pasted sheet will occur.

When you said you wanted to sort the original sheet but that skews the formula results ... copying the
values would be the approach.

Did you mean to accomplish something different ?

 

[bflan0524]

.
I understood you wanted to sort the data ... not continue to have the formulas perform their action.

By copying the VALUES only, you will be copying the values that are present at the time of copying.
Those values will be transferred to the new sheet allowing you to then sort those values. The formulas
will not be copied to the new sheet so no further changes to the values on the pasted sheet will occur.

When you said you wanted to sort the original sheet but that skews the formula results ... copying the
values would be the approach.

Did you mean to accomplish something different ?

so this is part of a report that gets distributed to a group of people, so when the people get the report they select their name from the drop down box which brings them to this dynamic page, its on that page where i want them to be able to press this button and have the sheet they are on filter for the columns i want from highest to lowest and lowest to highest

 

[Logit]

.
Ok .. better description !

Can you provide a sample sheet with the formulas in place and the values displayed ? You'll need to post it to a cloud site like
DropBox.com or similar so it can be downloaded.

Make certain there is no confidential information displayed.

Thanks.

 

[bflan0524]

.
Ok .. better description !

Can you provide a sample sheet with the formulas in place and the values displayed ? You'll need to post it to a cloud site like
DropBox.com or similar so it can be downloaded.

Make certain there is no confidential information displayed.

Thanks.

thanks for the response, i am not sure how i could post it without providing the confidential info. appreciate the attempt

 

[Logit]

.
You could replace the real names / acct #'s or whatever with false pseudo terms.

 

[DataBlake]

it just never works and sorts properly

As Logit has pointed out: this won't work if you leave the formulas as is. The only other workaround i see to this is locking the references in your fomula in place. In order to do that you just simply need to put "$" in front of cells and sheet names

So =sum(A5:A10) would become =sum($A5:$A10)
you would have to do that for every formula, but note that the values will still change if its referencing anything on the same sheet.
Without a confidential-safe workbook to see you're not going to get much help past that

i have a similar report that tells me missing values with constantly changing sheets
Another way to workaround references is to put them in quotes
so
Pricing!O2:O would be "Vision!O2:O"

 

[DataBlake]

So =sum(A5:A10) would become =sum($A$5:$A$10)

sorry this is probably more helpful. locking the row and column is what you want in your formula i think
when i said "cells and sheetnames" i meant "rows and columns"
pressing F4 when referencing cells in formulas also lets you change these values to $