Copy visible cells

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
I have a database which i have used subtotals on. Can I copy just the subtotals to another sheet without selecting the individual cells ie close the subtotal view to only show the subtotal values , highlight them all then copy and paste ?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy Pete, Try, alt-; -> Ctrl-c. Go to your next sheet, ctrl-v.
 

Blacky

Board Regular
Joined
Jun 9, 2002
Messages
105
Select the area you want to copy. Press F5 click Special, click visible cells only click OK. Copy, then paste where you want.

There is a toolbar button for visible cells only if you use it lots.

HTH
 

kleino

New Member
Joined
Aug 21, 2009
Messages
1

ADVERTISEMENT

after I choose "visible cells only" nothing happens.i.e. the paste does not show anything
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
after I choose "visible cells only" nothing happens.i.e. the paste does not show anything
Welcome to the MrExcel board!

The steps are:

1. Select the (collapsed) Subtotals range.

2. Alt+; (or Blacky's method) to select the visible cells only.

3. Copy

4. Move to where you want and Paste.
 

EMF

New Member
Joined
Jan 24, 2010
Messages
1

ADVERTISEMENT

Great help, thanks a lot.
 

MarkS123

New Member
Joined
Aug 27, 2010
Messages
1
Note: This only works if you paste into a worksheet that was opened in the SAME INSTANCE of Excel. If you try to paste into a worksheet in a new instance of Excel it will paste all the cells, not only the visible ones (for Excel 2007).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Note: This only works if you paste into a worksheet that was opened in the SAME INSTANCE of Excel. If you try to paste into a worksheet in a new instance of Excel it will paste all the cells, not only the visible ones (for Excel 2007).
Sure, but I guess that wouldn't be such a common occurrance. :)
In any case a few extra steps should achieve the same result.

After copying to the second instance of Excel ..

1. Apply AutoFilter
2. (Custom) Filter the relevant column for 'does not end with' ' Total' (or ' Count', ' Average' etc - depending on your type of SubTotal)
3. Select & delete all the rows below the heading row
4. Remove the AutoFilter
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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