Copy And Paste Function Messes Up Because Of Custom Sort / Filter?

Jun 5, 2013

what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.

Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.

In case this isn't clear enough...

Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10

Once I filter/custom sort, I see rows 1,2,3,8,9,10

When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.

View 6 Replies


ADVERTISEMENT

How To Create Custom Copy And Paste Function

Jan 15, 2013

How would I create a shortcut to automatically copy the contents of cell H1 to cell K1, in other word I would like to hit a shortcut key and have the contents of that cell pasted 3 cells to the right on the same line. I have to do this over and over again down the worksheet. I am working on taxes and want to copy values over to the expenses column as I find them.

I would also like to know if it is possible while having cell D1 highlighted I could have a shortcut created that would copy the value three cells over in H1 to cell K1. That would be the fastest, but I don't know if it is possible?

View 2 Replies View Related

Custom Sort / Filter Workbook By Specific Text

Jun 6, 2014

Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...

View 3 Replies View Related

Using The AutoFilter/Custom Filter Function

Feb 3, 2010

I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:

Filter:

does not begin with - 3

and / or

does not begin with - 9.

I want to add a third 'and' criteria .. is it possible, and if so, how?

The column that I am trying to filter has numbers formatted as text.

View 9 Replies View Related

VBA Custom Function To Sort And Average Two Numbers Of A Range

May 7, 2013

I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:

Original unsorted data:

79
45
23
56
1

The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.

I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:

Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer

[code]....

View 1 Replies View Related

Using Macros For Custom Filter Giving 'Or" Criteria And Paste The Data In Sheet2

Sep 25, 2008

When I go one column and Click custom filter and give the command one number and or another numbers ( I Have attached an excel sheet with screen shot) This filters the data, and I need to copy the same and paste in the next sheet.

I have to do like this for about 20 times for 20 sets of data). I have already done this and pasted the data in sheet2. I did everything manually. ( sample sheet is attached) I need a macro to do this work for me.

When I run the macro If get 2 text boxes I can enter the numbers. and click ok,the data has to filtered in sheet1, and result has to be pasted in the next sheet.with the header. Again I will run the macro i will give 2 numbers and the result should be pasted in sheet 2 after the 1st set of data leaveing one row as blank. ( exactly like the sample data in sheet 2). If I run the macro for 10 times giving 10 different numbers, the result should be pasted one after the other in sheet 2.

View 3 Replies View Related

Excel Sort / Filter Function Based On Set Criteria?

Sep 9, 2012

I have following data to sort/filter

Sector
Flt no
origin

[Code]...

Is this possible with excel functions?

View 1 Replies View Related

Custom Copy/paste - Cell Reference For Destination

Jan 30, 2007

i am writing a custom copy/paste routine which will paste data from a selection if the cell being pasted to is not locked.

The user will select the text for coping and then launch the routine.

The routine will query for destination cell and then process the paste.

This is my routine so far:

Sub testCopy()

Dim c As Range
Dim MyRange As Range
Dim Dest As Range

Set MyRange = Selection
Set Dest = Application.InputBox(prompt:="Select a cell", _
Title:="Paste Destination", Type:=8)

For Each c In MyRange
If Dest.Locked = False Then
Dest.Value = c.Value
End If
'dest = ?
Next c
End Sub

What I need to know is how to increment the dest reference so it is in the same 'relative' position from the initial dest that c (from the selection) is in or is there a better way to do this?

View 3 Replies View Related

Copy Text From Excel Worksheet And Paste It Into Custom Header

Jun 27, 2013

Is it possible to copy text from a Excel worksheet and paste it into the custom header or can you just type in the three boxes?

View 1 Replies View Related

Copy And Paste From Filter Data?

Jun 9, 2014

How can we copy and paste filter

For Example I have copied table data and want to paste same date in J and K column then how can I do this ?

File is attached for ref : Copy and Paste.xls‎

Actually my Basic question is how can I copy data from filtered data and paste in visible cells only

View 8 Replies View Related

Copy / Paste Into Pivot Filter VBA

Jul 2, 2012

I am trying to run a macro on a few items(24) in a large pivot table(6000). How can I run through a list, one at a time and then run another macro?

Here is what I have:

Sheets("Random Demand").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "D12549.256"
Sheets("Item Lookup").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("ITEM_NUMBER").CurrentPage _
= "D12549.256"

Run other code here, then loop back and choose the next item on the list

The problem is the ActiveCell.FormulaR1C1 = "D12549.256", the range if cells with the items I need to use is Range("O1:O24"), that is what I want to use since the data will change and needs to be dynamic.

View 2 Replies View Related

Copy And Paste Special While Filter

Mar 7, 2009

Do anyone know that if excel can be copy and paste special value when some of the columns are filter? It seems that you can only do copy and paste special value when unfilter everything column.

View 9 Replies View Related

Sort Command - Custom Order Or Sort Ascending Or Descending

Oct 8, 2006

Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.

Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E

If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.

Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B

View 4 Replies View Related

Sort Data Macro Then Copy / Paste

Dec 26, 2011

I am looking for a macro that will allow me to the following:

- search column F for "word1", "word2", "word3", and/or "word4"

- then match the search to row on column C data

- then copy/paste row or rows of matched data unto "Sort" worksheet but only data from columns A, B, C, and F

- also, when copying, copy the row above

View 2 Replies View Related

Sort, Copy Paste & Create New Sheets.

Feb 16, 2007

I have a file that i import into excel as fixed width. this is done with the code i have already written. Now im at the point where i need to "pretty" up the report.

In colum A there are Account numbers.

My goal is to have every row with the same act copied to a new sheet, and have the sheet named after the common value in colum A.

once its done there should be roughly 10 to 15 separate sheets.

View 9 Replies View Related

Sort, Copy And Paste Special Code

Feb 22, 2007

1. Alphabetize the worksheets that have a red tab color. The number of worksheets with a red tab color will vary from time to time.

2. All worksheets with a red tab color are formatted the same. I would like to copy the same range of cells (AP5:BP9) on all n number of worksheets with a red tab color to a worksheet labeled "Posting". Also, since AP5:BP9 are cells with formulas and formatting, the ranges need to be copied with formats and value and number formats. The first copied range needs to be on line 8 on the Posting worksheet, the second range to start on line 15, etc.

3. Last, but not really last, I would like to start the code with deleting whatever is currently on the Posting worksheet starting at line 8.

I have attached a small sample. The sample does not reference the same range as above on the worksheets with a red tab color, nor does it contain the formulas and formatting.

View 3 Replies View Related

Sort Worksheets Using Custom Sort To Choose Certain Word?

May 30, 2012

As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.

is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.

View 1 Replies View Related

How To Filter And Copy Data From One Sheet And Paste To New Worksheet

Jun 2, 2008

can excel do this (see the attachment pls). if possible can someone show me how to do that. i am new in excel vba.

View 9 Replies View Related

Filter, Copy Specific Columns, And Paste To Another Sheet.

Sep 27, 2009

Yeah it is 1:33 am where I am.

I'd like to filter a column and paste only 2 columns to another sheet in the first available row.
I found a code that was posted by Tom Ogilvy and made a few adjustments: ....

View 14 Replies View Related

Copy Rows Based On Criteria, Paste As Values To New Sheet & Sort

Apr 15, 2008

I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to

A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula).

B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting).

C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st

D) delete the copied rows from "current".

e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size.

I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work.

Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.

View 6 Replies View Related

Copy The Destination Data Using Some Sort Of Hyperlink Function?

Nov 10, 2009

I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.

The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.

Therefore my question is two-fold:

- Is there a way to copy the destination data using some sort of hyperlink function?
- If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?

I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.

View 9 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

Filter Results Of Primary Filter Sort

Feb 11, 2008

I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.

I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.

View 3 Replies View Related

Copy And Paste Function In An IF Statement

Feb 22, 2008

Can I write Copy and paste function in an IF statement
PTPO #Part #Line #DescriptionVendor #UOMLTPO Entry #Due DateAllowed DaysWork daysQty OrderQty RecvdUnder/ OverRectp dateDays Po lateMB550427917495072PANEL, SW MOLDED SLVR0BAY008EA204/16/20075/14/200728215014-365/7/200702013135/30/2007-280232023236/1/2007-28025

formulae in the column Q " days PO, Late" =IF(P3

View 17 Replies View Related

Excel 2010 :: Copy And Paste Function?

Apr 18, 2012

The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.

When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.

As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.

I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.

View 2 Replies View Related

Macro For Copy / Paste With Logical Function

Mar 13, 2013

I am working with some data that consists of two types of messages: one containing vessel speed and another containing vessel type. Both messages share a common ship identifier number. What I'd like to do is run through column A (ship ID), check column B (ship type), and if column b has a value, to paste that value in all instances of that ship ID.

View 1 Replies View Related

Color Function Not Work After Copy Paste From Other Sheet

Feb 18, 2010

First I want to copy the calculations from sheet "Calc" to sheet "Cost" then in column J in the same row, the background color will change like conditional format on numbers. The code works when copy the calculations from sheet "Calc" to sheet "Cost" but the color function (codes in sheet "Cost") does not work after pasted in sheet "Cost". But the color funtion does works when enter numbers in Column J alone. I know there must be a simple codes that can do this while paste from other sheet. This code is in module

View 3 Replies View Related

Copy Filter Data And Paste It On Another Workbook With Special Cells (Only Visible Cells)

Apr 12, 2014

I am using code to filter my 4 sheets Greater then 0 (zero)

After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:

for 1st sheet with the name ("V2")

for 2nd sheet with the name("LV")

For 3rd sheet with the name ("F2")

and 4th sheet with the name("L2")

If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well

But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.

So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .

View 5 Replies View Related

How To Do Custom Sort In VBA

May 20, 2014

I am trying to sort a sheet based on the values in a column.

S, SI, M, MI. and it need to be sorted only in this order.

View 2 Replies View Related

Custom Sort Using VBA

Aug 18, 2006

I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I dont know why its not working:

Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub

find attached an example, try entering 300000 in the inputbox the custom sort doesnt work

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved