I have a spreadsheet with a few hundred rows of data. I am looking for a way to copy a range of cells from one sheet to another. Multiple people will be entering data on various pages but only certain information (rows) is needed.
The sheets are generic with formulas entered into, say, the first 500 rows. Some people may only use the first 100 rows, others may use 300, etc. All sheets are identical in cell alignment, column names, etc. Only the "the amount of each sheet used" changes.
Since some people will have formulas with no values, I get N/A and #value errors. No problem. What I've done, is put in a formula at the end of each row to check if the next row starts with an "N/A." If it does, the last valid row displays the word stop in column AD.
Row 1 Data Data Data
Row 2 Data Data Data
Row 3 Data Data Data Stop
Row 4 N/A 0 #Value, etc
How do I copy just the VALUES, not the formulas, from "A1" to "ADstop" and paste it to a new sheet called Merge Information or something like that?
*** I forgot to mention first time I posted that this data would need to be sorted afterwards. Not sure if I should do this with a Macro/Button or if there is a way to have it automatically sort by column J by VBA. The people using this are not well versed in computers, nevermind Excel. I'm not sure if having them manually try to sort will create more problems than it's worth. ***
Thanks in advance
The sheets are generic with formulas entered into, say, the first 500 rows. Some people may only use the first 100 rows, others may use 300, etc. All sheets are identical in cell alignment, column names, etc. Only the "the amount of each sheet used" changes.
Since some people will have formulas with no values, I get N/A and #value errors. No problem. What I've done, is put in a formula at the end of each row to check if the next row starts with an "N/A." If it does, the last valid row displays the word stop in column AD.
Row 1 Data Data Data
Row 2 Data Data Data
Row 3 Data Data Data Stop
Row 4 N/A 0 #Value, etc
How do I copy just the VALUES, not the formulas, from "A1" to "ADstop" and paste it to a new sheet called Merge Information or something like that?
*** I forgot to mention first time I posted that this data would need to be sorted afterwards. Not sure if I should do this with a Macro/Button or if there is a way to have it automatically sort by column J by VBA. The people using this are not well versed in computers, nevermind Excel. I'm not sure if having them manually try to sort will create more problems than it's worth. ***
Thanks in advance
Last edited:
