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
ADVERTISEMENT
Apr 14, 2014
I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
View 11 Replies
View Related
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
Oct 22, 2013
I have a form with a number of fields. There is a field at the top of the form for Part number. When I type in a part number I need the form to auto complete any fields that are linked (if i can link them). E.g., if the part number is 111 then based on info in another table the fields will be auto completed for me.
View 2 Replies
View Related
Dec 6, 2009
can someone make a code for a range.Name = "match" as soon as active then that active cell is to be copied to cell C2.
View 9 Replies
View Related
Oct 27, 2008
I want to automate the following steps when cell A8:A11 changes in sheet "InfoAA":
(1) clear contents and formats of cells A1:A4 in sheet "InfoBB"
(2) copy cells A8:A11 of sheet "InfoAA" (which are formulas) and past it as text in cells A1:A4 of sheet "InfoBB".
(3) then automatically run a recorded macro named "BoldFirstName"
See attachment.
View 6 Replies
View Related
Jul 31, 2008
I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet
View 3 Replies
View Related
Nov 12, 2008
a VBA code to do the following:
I have a worksheet consist of some 17,000 records - from ColA:ColDZ. In Datasheet, I have all values. From this, based on some values in ColA all records should be copied into another sheet(s).
I have attached a sample workbook in which I have explained the requirement.
View 14 Replies
View Related
Jan 13, 2010
6 worksheets ("Monday" ,"Tuesday" etc)
1 Worksheet (call it Main)
In the Main sheet let's say there are 3 column headings - Date, Inv No, Amount
I want to copy from the Main sheet to the appropriate day e.g if the dates in the main are13/01/10 and 14/01/10 then all the info pertaining to 13/01/10 should go to the Wednesday worksheet.
View 12 Replies
View Related
Jan 9, 2007
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
View 9 Replies
View Related
Feb 13, 2014
Sheet 2 contains a drop down list containing names, would like to assign a Macro to a button on same sheet that would refer to sheet 3 based on certain criteria.
Sheet 3 contains columns A - K of data. 2 criteria, data in column D and column K. Column D would be the same as names in the drop down list, whereas column K refers to a constant, either y or n.
Example: If D contains "Cheddar" and K contains "Y", copy and paste A:K to Sheet1, preferably starting at row 7.
View 3 Replies
View Related
Jul 19, 2007
I am very new to Macro programming. I want to set up a macro which will copy data from few cells in a sheet and paste them as "Values" to certain specific locations in another sheet based on criteria. This copy paste needs to be done for about 40 different cells. I want to fix this with a button on the source sheet, so that this process happens when the button is clicked. I am attaching a sample file showing what I need.
View 2 Replies
View Related
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
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
Jul 2, 2009
I have a command button that I want to automatically copy and paste certain data to another worksheet when clicked. I keep getting a "runtime error 1004; application-defined or object-defined error". The code I have so far is:
View 5 Replies
View Related
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
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
Feb 16, 2007
I am have a spread sheet (sheet 1) with a number of columns and what i am looking to do is see all the people that are participating in a certain stage in the process that is not common, So what i am wanting to do is copy the names and the corresponding number of these people (on sheet1) over to a new worksheet(sheet2) based on a yes or no criteria further on in the spread sheet(sheet1). sheet 2 has additional columns that the workers here would need to fill in.
The criteria is in sheet 1 cells Y2:Y2000
The number is in sheet 1 cells D2:d2000
The names are in sheet 1 cells E2:e2000
If its at all possible i would also like to then make the names of those people on sheet 1 a hyperlink to the additional information
View 4 Replies
View Related
May 8, 2008
I need to be able to search column y and rows 5-100 and where cells contain "A" copy and paste entire row to row 110. I am guessing it is really simple but I am obviuosly missing something
View 3 Replies
View Related
Feb 18, 2012
I have a problem and I hope that it can be solved using a macro. Basically, I have a set of data in column A and B in sheet(1):
A
B
1NameDate
2JOY12/09/11
3MAR12/12/11
4DAVID12/12/11
5
I have another set of data in Sheet(2):
A
B
C
D
E
1NameDatesalary
CHARACTERRATINGS
[code].....
basically, I would take into consideration the names and dates in sheet(1) and if it matches sheet(2) then it would then paste their salaries, characters and ratings into sheet(1).
View 9 Replies
View Related
Mar 20, 2008
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
View 9 Replies
View Related
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
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
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
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
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
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
Jan 8, 2009
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View 2 Replies
View Related
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
May 14, 2014
I have a worksheet with some info that is filled in from other employees. I have Customer Name, Date of Oldest Invoice, Avg. Days Beyond terms in column N42:N44, with the data in column M42:44. I want to copy and paste that into another workbook, I need to transpose the data and then paste it into the next blank row. IF POSSIBLE, I would like to have some of the column headings the same as the above fields, and I would like to paste the data into the correct column heading.
Currently I have the code listed below. It copies the data, opens the spreadsheet, (this is where it fails) find the next blank row and transpose and paste the data. It does transpose and paste the data but it does not look for the next blank row, instead it just transposes and pastes the data in the last cell that was active when the work book was closed.
Code:
Private Sub CommandButton1_Click()' Paste Macro
' Macro recorded 5/14/14
'
'
Range("O42:O47").Select
Selection.Copy
Workbooks.Open Filename:="L:Financial Services! FRA !! Preston !3rd Party Collections Accounts 2014.xls"
[Code] ..........
View 8 Replies
View Related
