Paste Rows Based On Data In A Column

Jul 24, 2007

I've been using a macro that, among other things, will split a set of data into different sheets based on one column. For example, if I have Column A populated with numbers 1-10, each entry with a "1" in column A gets pasted into a sheet entitled "1", and so on. Some of the numbers I group together (for example, let's say I would group the 2s and 3s together).

I've been using the code below, but it seems to be very fickle. Half the time I run the macro, I get a "Run-time error 1004" and it points to the line "Sheets.Add. Name = WhtSht". Is there a different (easier) way to accomplish what I'm trying to do?

Dim rng As Range, StrtSht As String, WhtSht As String
StrtSht = ActiveSheet.Name
For Each rng In Range("A1:A" & Range("A65536").End(xlUp).Row)
WhtSht = rng.Value
If WhtSht = "21" Then WhtSht = "7"
If WhtSht = "34" Then WhtSht = "33"
If WhtSht = "36" Then WhtSht = "33"
If WhtSht = "37" Then WhtSht = "33"
If WhtSht = "56" Then WhtSht = "55"
If WhtSht = "57" Then WhtSht = "55"
If WhtSht = "76" Then WhtSht = "75"
If WhtSht = "97" Then WhtSht = "96"
If SheetExists(WhtSht) Then
Rows(rng.Row).Copy
Sheets(WhtSht).Select
Range("A" & Range("A65536").End(xlUp).Row + 1).PasteSpecial xlPasteAll
Sheets(StrtSht).Select
Else
Sheets.Add.Name = WhtSht
Sheets(StrtSht).Select
Rows(rng.Row).Copy
Sheets(WhtSht).Select
Range("A1").PasteSpecial xlPasteAll
Sheets(StrtSht).Select
End If
Next

View 2 Replies


ADVERTISEMENT

Vba Select Rows Based On Info In A Column, Then Paste Into New Workbook

Jul 7, 2009

I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable?

I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.

View 9 Replies View Related

Copy Paste Rows To Another Sheet Based On Column Criteria

Jan 17, 2007

I have a file that has two sheets, I have some formulas in the first sheet named "Data",What i would like to have is, If column G in the sheet Data is "Closed", then i want that particular row to be cut from the sheet " Data" and pated in to the Sheet "Done".

View 5 Replies View Related

VBA Code To Copy And Paste To Specific Rows Based On Matching Column Header?

Dec 7, 2011

I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.

I am trying to do this in VBA and I was wondering if there is a way to do this.

View 9 Replies View Related

Cut Data From Column And Paste To Rows

Nov 28, 2007

I'm in need of a macro that takes data from cells in column A and moves them into different cells. Each block of data includes a company name, contact name, address and usually (but not always) phone, fax, email, website, etc. I want to move each set of contact info into a set of cells that begin in column A, starting with company name. Refer to the attached spreadsheet

View 3 Replies View Related

Copy Data In Rows And Paste In Column

Apr 22, 2005

How do I create a macro that takes the 3 digits values in cells Q82:AJ86 and place them is column L, set code to start placing result in L38?

example
Q82=610
R82=611
S82=612
T82=613
Results
L38=610
L39=611
L40=612
L41=613

View 9 Replies View Related

Copy And Paste Data Based On Yes Or No In Column?

Jan 30, 2014

I have an Excel Spread Sheet that lists all of the people who have been issued Keys in your workplace, so the row has multiple information columns (Name, Department, Key Code, etc). One row is titled "Left Workplace" and you can select either a Yes or a No. Based on that selction I want it to copy and paste into a different spreadsheet (Either Inactive - if "No" is selected or Active - if "Yes" is selected) What is the best way to go about this?

View 2 Replies View Related

Copy And Paste Values Based On Column Data

Jan 28, 2014

I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?

[Code] ......

View 3 Replies View Related

Pulling Rows Of Data Based On Criteria In One Column?

Dec 17, 2013

I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).

View 6 Replies View Related

Consolidate Rows Of Data Based On Single Column?

Aug 16, 2014

I have thousands and thousands of rows of data which look like the spreadsheet screen shot shown in this image:

ExcelConsolidateRaw.JPG

I want to consolidate them so that they look like the spreadsheet screen shot shown in this image:

ExcelConsolidateDesired.JPG

How to insert it into a module in the VBA editor.

View 3 Replies View Related

Delete Rows Based On Seperate Column Data

Nov 29, 2006

How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.

View 4 Replies View Related

Macro To Copy Rows Based On Moving Date And Paste Rows Into Identical Sheet

Jan 28, 2014

I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)

e.g. 28/1/2014
28/1/2014
28/1/2014

I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.

View 9 Replies View Related

Condition Paste Formula In Column N Based On Column A

Sep 7, 2009

I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .

I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.

View 2 Replies View Related

Cut And Paste Rows Based On Color

Dec 21, 2009

I have an excel sheet with over 10,000 rows. Some of the rows are colored. (Light Green). I want a macro which starts from 1st colored row, cut or copy all the rows (including the colored row) before next colored row comes and paste it in the same workbook but different tab. It would also be good if macro creates the tab itself.

View 9 Replies View Related

VBA - Copy And Paste Rows Based On A Condition?

Apr 19, 2013

I have tried to write some VBA to copy and paste rows based on the condition in cells A:A in sheet one. So far I have managed to put something together which will paste the first row and insert a new row below but keeps on pasting the same row. But not much else! Not sure I have tried to make it more difficult than what is it.

I would like it to copy a row based on a certain condition ("WRW") in A:A sheet one and paste it to sheet2.

VB:
Function DoOne(RowIndex As Integer) As Boolean
Dim Key
Dim Target

[Code].....

View 3 Replies View Related

Cut And Paste Rows In Excel Based On Cell Value

Jun 13, 2013

I am working on a macro that will cut and paste rows into different worksheets based on a specific value. I have many different criteria, and though it is working, Excel freezes quite often and the macro itself takes over 10 minutes to run.

Dim LR As Long, i As Long
With Sheets("Payout_Check_List")
LR = .Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("F" & i)

[Code] ........

View 1 Replies View Related

Copy And Paste Rows To New Sheet Based On Dropdown Value

Mar 19, 2014

I have a workbook with 4 sheets relating to areas and a summary sheet and introduction.

On the introduction sheet I have a dropdown which lists all the available products my company make.

What I want is to be able to select the product from the dropdown and then click a button which will then copy and paste the headers and any rows which relate to the selected product from each of the area sheets and paste it all onto the summary sheet.

View 14 Replies View Related

Copy And Paste Down To All Unhidden Rows In A Column

Sep 16, 2006

I dont know if a vb code is at all possible to copy a formula in a cell in row 2 straight down that column until the last unhidden row in which there is at least a cell populated with values from the 256 cells in that last unhidden row. Then in that column, I want the cells that are filled with formulas to be converted to values.

All these unhidden rows are autofiltered with certain criteria. The left and right columns to the formulated cells does not necessarily filled with values as they can be blank. Row 1 has header names, and a formula can be any cell in row 2 at any one time and that is exactly the problem i faced so that to record a macro seems totally useless for me because the cell in row2 where I will begin with a formula varies every time.

Currently I go a long way by taking these steps:
Hghtlighting row1
Data/Filter/ Autofilter/
Select the header cell and click on drop-down filter
Select (Custom...) with criteria
Input formula on the cell in row2
Copy the formula
Press Shift+ArrowDown keys until the last unhidden row
Press Enter
Undo Autofilter
Select the column
Copy and Paste as values
Autofilter row1 again
Select the header cell, (Custom..) equal "#N/A" and "zero"
Continue next turn of formula..copy to last unhidden row, paste as values....

Me and other staff in my department are doing these long-winding steps endless of times, day in day out, until we are drained out and called quit. If there is a code that can do this, this is going to the greatest news ever for these people for that kind of work.

View 5 Replies View Related

Copy Paste Multiple Criteria Based Rows To New Sheet

May 23, 2008

I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.

So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5?

I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.

View 14 Replies View Related

Copying Only Rows With Data In First Column - Skipping Rows With Blank First Column

May 12, 2014

I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.

I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.

View 4 Replies View Related

Compare Rows In Column A And Copy/paste If They Are The Same Using A For Next Loop..

Jul 26, 2009

I have one column of names in excel. The column may contain more than one row with the same name but these rows with the same name will all be grouped together. This is an exampe (each name represents a row in column A):

ColumnAColumnB
andrewData
julieData
julieData
julieData
jonathanData
jonathanData


What I want to do is copy the rows with the same information, e,g, the rows with 'julie' above, paste them into a new spreadsheet and email this spreadsheet to specific email addresses and then do the same for 'jonathan'.

I can work out how to send an email using VBA but I am really stuck as to how to go through the rows and send the email in discrete 'chunks'. I have tried using a for next loop, looping through the rows and copying/pasting rows that are the same as the previous one into a new spreadsheet but this does it one row at a time.
If I include the instruction to email the spreadsheet within the loop this would also email the new spreadsheet one row of information at a time, i.e. three emails for 'julie' each containing a spreadsheet with one row of information on it, rather than one email containing one spreadsheet with all three rows on it.

View 4 Replies View Related

Copy/Paste Rows By Numerical Range In Column

May 8, 2008

I would like it to scan an entire column (column E) for rows of data that fall into a range of numbers chosen by the user (like maybe a list box or combo box in sheet 1) for example a number greater than or equal to 8 but less than or equal to 15 and copy all of the rows and columns of data across the entire sheet that fit the range from sheet 1 to sheet 2. I would like to use a separate list or combo box for the min and max values in which to search as seen in the example sheet 1 attached. I have tried the autofilters, custom filters and macro recorder with no success as it seems the filters do not like ranges of numbers.

View 3 Replies View Related

Pull Column Data (Sheet3) From Master.xls And Paste To Column 4, Sheet4 Of WorkingSS.xls

Feb 16, 2008

Pull Column Data (Sheet3) from Master.xls and past to Column 4, Sheet4 of WorkingSS.xls

I'm assuming this would be done with VBA or a really exotic macro.

The Funky Part would be that the WorkingSS.xls file column data is being copied/pasted too (WorkingSS1.xls or WorkingSS2.xls ect) the file may be different every time so I would need an insert in macro or VBA to "Choose File Please..." then continue.

The Master.xls workbook has spreadsheet lets say "Sheet1" in which I need all the data in Column A (except the header or cell A:1) copied TO WorkingSS1.xls on Sheet4, Column B, but Column B already has about 6000 rows of info, so I need it copied to the very end of (A:6001 although it will be different everytime) or the first empty cell at the bottom of that column.

next another Column from Master.xls workbook lets say "Sheet1" again in which I need all the data in lets say "Column B" copied to the WorkingSS1.xls on Sheet4, Column F. Caveat this time is that the data needs to copied to the same row as the first copy/past. So it would be pasted into F:6001. Double caveat is that the Column F contains no other data except for what we are about to paste in.

I have several more steps of automation to be done here but this is the beginning and a big hump I need to get past. The rest I think I can do.

View 9 Replies View Related

Macro To Copy/paste Specific Rows (and Formulas) Based On Drop Down

Sep 8, 2009

I have about 10 rows of data that have columns of information describing computer hardware and pricing. There are 10 rows, because each row represents a different country and different tax rates and pricing changes per country. So each column of a row has a specific formula to calculate information for that row's country information. All these 10 rows of formulas are kept as a reference at the bottom of the sheet.

At the top of the sheet I need to have a drop down menu to choose a country and then it will automatically copy and paste the row of that country to the current row at the top.

Is there any easy copy/paste macro for such a thing?

View 9 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/paste Row To Another Sheet Based On Value In Column

Mar 16, 2009

I've two sheets: "present" and "removed". I would like to have a whole row copied and deleted and existing rows moved up in "present" when I type the word "yes" in column E. The deleted rows are to be pasted into "removed", with two extra pieces of data - date removed and name of person removing it - to be asked in pop up boxes and inserted into col F and G respectively in "removed".

View 4 Replies View Related

Paste A Value Within A Row Based On Column Lookup Containing Dates?

Oct 27, 2009

I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row.

I have weekly dates in columns along the top of my spreadsheet on row 3:
C3 = 07/11/08
D3 = 14/11/08
E3 = 21/11/08
etc...

The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B.

I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row.

For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18.

I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.

View 6 Replies View Related

Vba - Formula Based On Used Column + Paste Values

Oct 12, 2009

1. In this formula in column C, below, i have A2 as the look up... sometimes this will be A3 , A7 or other... how can i vary this formula? I've tried it all ways.

Range("C2:C" & LR).SpecialCells(4).Formula = "=VLOOKUP(A2,Data!$A:$B,2)"
It need to be inserted based on the first cell in column A which has a value.

2. after this has done its thing... how can i paste the values to ONLY those cells which have been affected by the formula. I don't want to copy, paste special the whole column as there are sub total and other exciting things included.

View 9 Replies View Related

Paste Formula Down Column Based On Last Cell In Another

Jan 29, 2008

I am trying to get a formula that is sitting in cell A2 to be pasted all the way down column A as far down as B values exist.

View 5 Replies View Related

Paste Formula Across Rows But Change Relative Cell Reference To Column From Row

May 26, 2009

I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...

View 2 Replies View Related







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