Select range of cells and paste to a new sheet using formula or VBA

yoda_pooh

Board Regular
Joined
Oct 12, 2007
Messages
72
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
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Since you have that "Stop" as the last item in the AD column, you could use code thike this:

lastrow = Cells(Rows.Count, "AD").End(xlUp).Row
MyRange = "A1:AD" & lastrow
range(MyRange).copy
 
Upvote 0

Forum statistics

Threads
1,196,057
Messages
6,013,178
Members
441,752
Latest member
klfezler

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
Back
Top