macro help

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
174
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,855
.
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

Board Regular
Joined
Oct 7, 2016
Messages
174
.
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,855
.
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

Board Regular
Joined
Oct 7, 2016
Messages
174

ADVERTISEMENT

.
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,855
.
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

Board Regular
Joined
Oct 7, 2016
Messages
174

ADVERTISEMENT

.
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,855
.
You could replace the real names / acct #'s or whatever with false pseudo terms.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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"
 
Last edited:

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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 $
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top