Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Copy and paste only the rows with subtotals

I have a spreadsheet where I can click on the "1" "2" "3" buttons to the
left to make different summary levels appear.

When I click on "2", I have just the five rows with subtotals visible.

I want to just cut and paste those just these rows rows to a different
spreadsheet, but when I paste, all the rows are pasted in, not just the
subtotals.

Is there a way to just paste the subtotals?

Thanks,
Alan

--
achidsey


Post your answer or comment

comments powered by Disqus
I have a spreadsheet where I can click on the "1" "2" "3" buttons to the
left to make different summary levels appear.

When I click on "2", I have just the five rows with subtotals visible.

I want to just cut and paste those just these rows rows to a different
spreadsheet, but when I paste, all the rows are pasted in, not just the
subtotals.

Is there a way to just paste the subtotals?

Thanks,
Alan

--
achidsey

When I have a spreadsheet with subtotals, sometimes I just want to copy the
subtotals into another sheet and forget about what is being summed to give me
the subtotal. How do I copy and paste only the subtotals.

I have subtotaled a document and now I want to copy and paste only the
rows that are showing. Is there a way to NOT copy and paste the hidden
rows? Any help is MUCH appreciated!

--
tracykrulich
------------------------------------------------------------------------
tracykrulich's Profile: http://www.excelforum.com/member.php...o&userid=28076
View this thread: http://www.excelforum.com/showthread...hreadid=475832

I have subtotaled a document and now I want to copy and paste only the rows that are showing. Is there a way to NOT copy and paste the hidden rows? Any help is MUCH appreciated!

i have an excel doc. that has hdden and unhidden information. i want to
copy and paste ONLY the unhidden information. When i copy and paste it it
takes everything hidden with it. how to i stop that from happening.?

I want to select rows from a worksheet with some rows hidden, copy, and paste
only the contents from the rows which are visible. How is this accopmplished?

Hi.
Suppose I filled cells from a1 to a100 with numbers from 1 to100 except a8 which still empty and ,also, other cells
So, I want to select the column (a) then copy and paste only those filling cells except the empty cells.
I thought I can use "paste special" and choose "skip blank" to get only the filling cells, but I failed!
Can you help me!

is there a way to copy and paste only the values of cells with data in them in the current worksheet to a new worksheet?

im working on this now and will post back if I come up with something, I can check for values but am having difficulty checking each and every cell for values..im stumped for now

thanks!
-Ins

I will try to explain this as best I can. I will first say I inherited this "report" so it's not my design at all and I'm just trying to speed up a daily process. The workbook has 30 sheets. Each day I have to go to 20 of the 30 sheets, find a specific date located in column A, copy the entire row, and paste the values right back into the line so I delete the formulas. like I said, don't ask why if possible . It's how they set it up.

I'm trying to build code that will run through each sheet I designate, find the cell in Row A, then copy and paste only the values. I got the code to work for one sheet. But, I cannot get it to work when I want it to run through sheet 23, 24, 29 etc.


	VB:
	
 Find_First() 
     '
     ' finds date in Column A and cut and pastes line
    Dim FindString As String 
    Dim Rng As Range 
    FindString = InputBox("Enter a Date") 
    If Trim(FindString)  "" Then 
        With Sheets("Split 150_880").Range("A:A") 
            Set Rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlValues, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not Rng Is Nothing Then 
                Application.Goto Rng, True 
            Else 
                MsgBox "Nothing found" 
            End With 
             
        End If 
         
        Range(Selection, Selection.End(xlToRight)).Select 
        Selection.Copy 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Application.CutCopyMode = False 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm sure this has a simple solution....I have a large worksheet that has a
lot of hidden rows. I want to copy and paste only the unhidden rows and can't
find a 'paste' function to do it.

Hello ladies and gentlemen

I know this is a simple solution but there is some aspects of VBA that I still can't get me head around, therefore I am now stuck.

Project Gantt_BCP.xlsm
Cells A8:A24 in Sheet "Help" is a list of items which now and again I would need to copy and paste below the last row in column B in Sheet "DailyPlan". I would like this in the form of a button, so that I click the button and hey presto the 17 items are pasted immediately in the last 17 rows.

Can anyone give me a quick detail how to do this?

Ta

Hello,

I have a workbook that contains only 1 worksheet. Within this worksheet I
have rows that contain the word "NONE". I need to put all the rows that
contain the word "NONE" on their own worksheet. I have been able to create
the worksheet using VBA, but I am am having problems cutting the data from
one worksheet and pasting it onto the newly created worksheet. I have tried
creating a named range for the column that contains the word "NONE" and then
referencing that column using the EntireRow method to try to select the rows
that cointain the word "NONE" and paste them onto the new worksheet. How can
I cut and paste just the rows that contain the word "NONE" from one worksheet
to the other worksheet? Any help, suggestions or useful links will be greatly
appreciated. Thank you.

Dave Y

Hello,

I have a workbook that contains only 1 worksheet. Within this worksheet I
have rows that contain the word "NONE". I need to put all the rows that
contain the word "NONE" on their own worksheet. I have been able to create
the worksheet using VBA, but I am am having problems cutting the data from
one worksheet and pasting it onto the newly created worksheet. I have tried
creating a named range for the column that contains the word "NONE" and then
referencing that column using the EntireRow method to try to select the rows
that cointain the word "NONE" and paste them onto the new worksheet. How can
I cut and paste just the rows that contain the word "NONE" from one worksheet
to the other worksheet? Any help, suggestions or useful links will be greatly
appreciated. Thank you.

Dave Y

How do I copy and paste non-adjacent rows in an Excel spreadsheet to a new
spreadsheet?

Hello!

I already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:


	VB:
	
Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls" 
On Error Resume Next 
With Workbooks("Example 2.xls") 
    LR = .Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row 
    Workbooks("Example 1").Sheets("Data").Range("A1:BT65536").Copy 
    Windows("Example 2.xls").Activate 
    Sheets("Raw Data").Range("A" & LR + 1).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.

Any advice?

Thank you for your help!

Jace

I run a report every week that is exported to EXCEL. Once there, I check the
listed data against hard copy records that we keep and if the hard copy is
present, I enter the date that record was generated in a corresponding column
in my spreadsheet. Every time I run the report, I add it to the same sheet
and it is getting very long. To manage this, I use the autofilter function
to display only the rows with the date column containing information (all non
blanks). Then, I cut the displayed data and paste it into another sheet.
The problem is, when I do the cut, it cuts the displayed information as well
as the hidden information. Is there a way to leave the hidden info and cut
only the displayed info? Or even create a macro that will automatically move
the row that has this column populated? That would make me an even bigger
hero. Thanks in advance for your condideration.

Don F.
--
Fishman4

I have a spreadsheet with various test results. I ask the user to copy
all (only option) the records once a day and paste into these into
spreadsheet from application. What I would like is for when this
occurs a macro that deletes all data unless it is new into another
spreadsheet. Then I will export only new data to a sql table. Any help
is greatly appreciated.

I filter out rows and copy data from the filtered rows. However, when I
paste the data onto another column, the data gets pasted on the rows which
are hidden. How can I ensure that the data is pasted onto the same rows?

Eg. I have 3 rows of data:

Name Amount Column 1 Amount Column 2
Row 1 A $2
Row 2 B $5
Row 3 A $6

After filtering for Name = A, the spreadsheet will show only datas on Rows 1
and 3.

This shows:
Name Amount Column 1 Amount Column 2
Row 1 A $2
Row 3 A $6

Now, I want to copy $2 and $6 onto Amount Column 2, so I highlight the two
cells and click on 'Copy'.

However, when I paste the amount on 'Amount Column 2', it will appear as:
Name Amount Column 1 Amount Column 2
Row 1 A $2 $2
Row 3 A $6 (Blank)

.... Because the $6 has been posted onto Row 2, which, when i unfilter all,
will show:
Name Amount Column 1 Amount Column 2
Row 1 A $2 $2
Row 2 B $5 $6
Row 3 A $6

How can I make sure that, with the filter still on, the $6 gets pasted onto
Row 3?

Many thanks.

im using this code :

Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 0, 2), "ThisWorkbook.Moverow"
End Sub
Sub Moverow()
Worksheets(1).Range("A1:b1").Copy
Worksheets(1).Cells(Rows.count,1).End(xldown).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
Application.OnTime Now + TimeSerial(0, 0, 2), "ThisWorkbook.Moverow"
End Sub
and im having an error 400

i want to copy a row and paste only the values downwards to the last active row

I have managed to write a program to calculate golf handicaps with much appreciated help from you folks. However, there is one aspect of the program I would like to improve and request your assistance once more. I have attached a diluted sample. It is very tedious to extract info from the Scores worksheet to the Posting worksheet in that I only know to copy and paste from the appropriate cells. The Posting worksheet will actually have 5 rows per player (as it will list a maximum 20 scores by date in descending order) and needs to be in alphabetical order. Any suggestions on how to handle this more efficiently? Thanks in advance for your assistance.

Teelinks

Hi

I've been working through a spreadsheet, with no small amount of help from you guys, and got a little stuck again. I need to find a way to look at a spreadsheet and copy only the rows with data in column A. There will be no instances of blank lines in between complete lines.

Thanks for reading

Bert

I want to cut a row containing the word “week1” along with the row below it. I then want to paste these rows in the first blank cell after A3.
I have figured out how to cut only the row with “week1”, by doing a find and then selecting entire row, but I have not figured out how to get the row below it and paste in that row with the blank cell.
Please Help!!

I apologize in advance for this lengthy question. I'm trying to use a macro to automate a monthly routine I perform. I have a workbook named “My Model.xls” containing a number of worksheets named vintage 1, vintage 2, etc. Each month I receive a file named “Monthly Report.xls” formatted exactly the same as “My model.xls” with exactly the same tab names and each tab is also formatted exactly the same, i.e. data in columns and rows are the same, except with one additional monthly information, which I need to copy and paste value into “My model”.

In this example, this worksheet named “vintage 1” in “My Model.xls” has columns containing monthly data of “200701, 200702, 200703, 200704” from column C to column F. In the “Monthly Report.xls” file with same worksheet name “vintage1”, it contains one additional monthly data “200705” in Column G

I need to:
- Find the worksheet named “vintage1” from “Monthly Report.xls”
- Copy last column data “200705” in column G
- paste value into Column G in worksheet named “vintage1” in “My Model.xls” immediately after the last column’s data, i.e. 200704.
- Repeat the above process for all the tabs, i.e., vintage1 to vintage20.

The number of columns are different in different worksheets. For example, while worksheet “vintage1” contains data from 200701 to 200704, i.e. from Column C to Column G, worksheet “vintage 2” may contain data from 200608 to 200704 (i.e. Column C to K), and therefore the last column that containing data are different for different vintages. Therefore for vintage1 I need to paste the new 200705 data into column G, and for vintage2 in this example I need to paste into column L.

I’m sorry I was unable to attach an excel worksheet example with this question. I can email you the attachment if necessary. Any help will be greatly appreciated.

Just a little query.

I'm using a spreadsheet with about 13 worksheets in it.
The 1st is a Main Menu, which uses Userforms.
and then I have a sheet for each month and a sheet to hold a lot of data.

When the user presses a button on the Userform on the Main Menu I want the macro to be able to copy and paste information from one place to another on one of the other sheets without the screen flicking to the sheet where the data is being copied.

Now.... I have done this using the following code.....

Worksheets("Database").Range("AE318:CH318").Copy
ActiveSheet.Paste Destination:=Worksheets("January").Range("AE" + CStr(RowNumber) + "")

The Destination method puts it where you want the information to go (in the background) and the user is one the wise that this has happened.

However I want to be able to do pasting (formulas only) using the PasteSpecial command, however PasteSpecial doesn't support the Destination parameter. Therefore when the macro pastes information to 3 different sheets the screen flicks from one sheet to the other and then returns to the Main Menu screen, which looks a bit naff to the user.

Is there any way to get round this?

Thanks

Matt


No luck finding an answer? You could always try Google.