Copy Range Based On Certain Conditions

Sep 5, 2006

I have this macro to copy a range to another worksheet:

Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y
' las cajas ATR
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))
Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))
rng1.Copy
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
rng2.Copy
Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True
End Sub

The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.

View 9 Replies


ADVERTISEMENT

VBA - Copy Range Of Rows With Conditions?

Dec 17, 2012

I'm trying to code a clean up of some data ranges ready for priting / reporting. I need to move filtered data from the source sheet, to a new one.

The issue is, that I need to apply some logic to the rows I filter out, not something I can do with autofilter...

- Only copy rows including and BETWEEN the first and last rows where total count in col 8 is > 0
So in example1, I'd want to copy out rows P6 - 1A

BUT
- Always copy at least 5 rows when there is one value > 0 in col 8

So in example2, I'd want to copy out rows P5 - 1C, ideally...

Total
Boys
Girls
Total

[Code]....

View 3 Replies View Related

Copy Cells To 2nd Sheet Based On Conditions

Apr 17, 2012

I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.

Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.

Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.

View 3 Replies View Related

Copy And Paste Cell Based On Conditions

Jul 9, 2012

I have data in columns A and B.

If the data is in column A it will not be in column B.

My objective is to move the data in column A to column B, as long as it is not a number with the color yellow in the cell.

The data displays as follows:

**SPECIAL NOTE: Cells with 34407211756830 and 34407212013659 have yellow cells.

Column A Column B
34407211756830 000 Staples IV

RC 636

View 1 Replies View Related

Sum Based On Multiple Conditions & Variable Range

May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

View 2 Replies View Related

Return Range Address Across Different Columns Based On Conditions

Apr 23, 2008

I am trying to return a cell address range based on values in different columns. Attached is an example.

In Column A1 I have "Range" and below that a set of numbers from 1 to 31.

In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.

What I want to do is as follows:-

I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.

I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.

View 9 Replies View Related

Fill Range With Data Meeting Conditions Based On Cell Value

Apr 23, 2009

refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View 9 Replies View Related

Fill Multi Columned ListBox Based On Range Meeting 2 Conditions

Mar 14, 2008

I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View 4 Replies View Related

Copy Range Based On Date

Feb 3, 2014

I have a bunch of open files with dates in cell A3. I want to copy the range starting in A7 (the same range and size in all the open files) and paste it into my vbafile based on matching the date (in row 11). I have a spreadsheet attach with an example of what the code would do for Jan 1.

View 5 Replies View Related

Copy Every Nth Range Based On Criteria

Feb 15, 2010

I am copying from cell A2 to C2, then A8 to C8, etc. if the cell in column "A" contains the text "Item" until the end of data in column A. Here is what I am attempting:

Sub MoveItem()
sText = "Item"
sText = LCase(sText)
iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = iMax To 1 Step -1
If InStr(1, LCase(Cells(i1, 1)), sText) <> 0 Then
Cells(i1, 1).Copy Destination:=Cells(i1, 3)
End If
Next i1
End Sub

The problem is the destination location. If I define it as a Range eg: Cells(i1, 1).Copy Destination:=Range("C2") The macro works, but does not iterate the destination cell.

View 3 Replies View Related

Copy Range Based On Criteria That Changes Constantly

May 7, 2009

I am trying to copy a range of cells, based on a set of criteria that changes constantly.

In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.

Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.

View 4 Replies View Related

Copy Cells To Different Sheet Based On Range

Mar 22, 2012

I need to make a VBA Macro (Assigned to a button) that will copy the information under the "Cut Length Worksheet" and paste it into the appropriate columns in our "Bill Of Materials Template." Any way to copy the cells, across different rows, into the proper cells.

I have several different Macros and Workbooks made up now, but I am lost here.

I also need the Cut Lengths to add certain info to the cell when it is copied:

8 | 7-15/16 ---> 8'- | 7-15/16"

Here is a Link to the Excel page.

View 3 Replies View Related

VBA Select Range To Copy Based On Two Variables

Jan 13, 2014

I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.

For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.

What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between

Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long

[code]....

View 3 Replies View Related

Copy Rows In Range Based On Criteria

Jul 14, 2006

which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)

But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?


Private Sub CommandButton1_Click()

Dim rngFind As Range
Dim strFirstAddress As String

With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If ..................

View 9 Replies View Related

Copy Range Based On Value To Closed Workbook

Jul 28, 2007

I would like to be able to have a user execute a macro by clicking a command button that would copy some named ranges (Name1,Name2,Name3) BUT ONLY the rows/cells that have "True" in column A. The destination for the pasted data is a closed Workbook named Data – Sheet1 located on a network drive (\NetworkTemplate)

Example:
One named range are cells D10:D50, a second named range are cells F10:F50, and a third is G10:G50. – But ONLY rows 11, 13,14 have True in column A, so I only want to copy the 9 cells not the entire row from those select ranges and open a closed workbook and paste the values only starting with cell B10.

I can not find anything that looks for a value in one column and only copies select cells to another workbook.

View 4 Replies View Related

Copy Range To New Workbook Based On Cell Value

Dec 20, 2007

to add additional questions to this link it says that the thread is expired so here i have posted a new thread.

Copy Cells To New Workbook If Match Criteria what i need is to copy the cells in new workbook "as is" i mean copy only the values and not the formula.

View 5 Replies View Related

Copy Range X Times Based On Cell Value

Jan 8, 2008

Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??

View 3 Replies View Related

Copy Or Clear A Range Based On Checkbox

Jan 30, 2008

I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.

View 2 Replies View Related

Copy Data Over Based On Reference And Named Range?

Jul 22, 2014

I got a very technical sheet which needs your experience to work.

open the attached file below to see the workbook.

It has 3 different sheets. "RESULT", "INPUT" and "SWITCH"

On "RESULT" Cell (O11) is where the person would put a Reference.

This reference is then checked on the "INPUT" sheet column A.

From the first cell it appears in Column A, until the last cell it appears in Column A should be the Row Range (Blanks included).

But I want that range (Column A to G) to be copied to "Switch" sheet. Pasted in Cell A1 downwards.

In the "SWITCH" sheet I have already given all the GREEN Cell formated areas a name - INFO.

I want all the data in INFO to be inserted into "RESULT" sheet between row 18 and 19.

I have colour coded most of the areas for you.. And added a button from which the Macro / VBA should run when clicked.

View 1 Replies View Related

Copy And Paste A Range Based On A Cell Value That Is Not Static?

Aug 8, 2014

Task:A user has to copy and paste a range of cells, if any row in column B has "PM" as a value.

I have several macros/VBS scripts that copy and paste from one workbook to another, how do I create a macro that says whatever row "PM" is in copy range F:BC on that row

My issues is the fact the PM can be in any row in column B in different sheets. Example.xlsx

View 6 Replies View Related

Copy Rows To Sheet Based On Cell Value In Range

Aug 16, 2007

a macro button on my excel sheet that should do the following.

i have a range (Ex: E3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E".

after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)

View 9 Replies View Related

Copy Range & Paste Based On Date Criteria

Jan 22, 2008

I have 2 ranges: 1."Task info" (4x4 range)
2." Dates" (24x2 range)

I have one variable cell ref: "Date1" (a date)

Based on "Date1" I want to copy the value in the "task info" and Paste special value that is next to the corresponding date in "dates".

View 4 Replies View Related

Copy Range Of Cells Based On Condition To Another Worksheet

Apr 17, 2008

"copy cell range based on conditions" and it didnt really get an answer. There was one that copied the info the next blank cell on that line, but im not smart enough yet to figure out how to copy it over. ( getting there though with lots of staring at code).

2 sheets. I have already created auto modules to fill in data and code,and sorted the columns so they are in line.

Sheet 1. Info : has 9 columns. So if column = 9 and the value = new.
Then i want to copy the range on the columns (A:G) and then paste it on the other sheet ( Card info) as long as Column A is empty ( as in next available blank cell) ( something like a DO while worksheet("Card info").column(A) <> ""

View 9 Replies View Related

Copy Formula Across Based On User-defined Input Range

Apr 4, 2008

I have a cell B10 which contains a formula.

I would like a macro which copies this formula across the same row.

However, the macro should prompt the user to select a range.

Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.

View 9 Replies View Related

Copy Data Based On Date Range Put In By User For Report

May 16, 2006

I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post.

Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.

View 9 Replies View Related

Copy Named Range Data To Worksheet Based On Dropdown Or Combobox

Feb 10, 2013

I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.

Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.

View 9 Replies View Related

Copy Rows To New Sheet Based On Date Range Selected On A Form

May 8, 2009

This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:

With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.

View 7 Replies View Related

Automatically Copy/Paste Data From Input Range Based On Criteria

Sep 4, 2006

I have a workbook with 2 - worksheets "DATA" and "LETTER".

The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.

The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.

After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.

In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....

View 9 Replies View Related

Automatically Copy & Append Range To Another Sheet Based On Number Entered

Mar 15, 2008

I need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.

View 6 Replies View Related

Copy And Paste Range Of Cells Based On Number Of Cell Values In Column A

Mar 17, 2014

Please see attached sample worksheet. Column A will be generated by the user manually.

I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.

I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.

Book2.xlsx‎

View 3 Replies View Related







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