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).
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: ....
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.
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.
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.
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.
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.
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.
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".
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company ------------------------------------------- 1/1/11 Jane A Company 1/1/11 Jane B Company 3/1/11 Bob B Company 2/1/11 Bob B Company 4/1/11 Bob B Company 5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
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.
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
Spreadsheet contains 15 worksheets, all named differently, "apple", "pear" "plum" etc etc. Worksheet has data columns A to E including in column E a "Next Review Date".
On worksheet 16, called "Crumble" .... I want to click a macro that will cut and paste in all the data from the 15 fruit worksheets where the date is
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.
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
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.
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange If Application.IsNA(b.Value) Then Employee = b.Offset(0, -2).Value SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found") b.Value = SSID End If Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click() Unload UserForm1 End Sub
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
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?
This code basically copies data from one worksheet to another worksheet based on the date and works great.
However, I have been asked to changed the format of the spreadsheet and the code needs to be changed. The code is in "This workbook".
The code takes the data from the summary tab and copies it to the archive tab. I now need it to take it from the summary tab and copy it to the archive 2 tab.
I have attached a copy of the spreadsheet. Data and Archive2.xlsm
The idea is to find the new references that have been created every month in sheet 2 and paste into Sheet 1. How to find the new ones is easy .However I don't know how to move the data around sheets!
Sheet 2 (A1: AQ25135) A B C D
FIND NEW PO Purch.DocItem Vendor Vendor Name
200095185710 200095185710 15152695 UAB Pajurio Gabija
[Code] ......
I need to do the following:
Whenever you find the word "NEW" in a row of sheet 2 in range A2:A50000 copy the row from B to AM and paste it into sheet 1 Column A to AL.
However Column A contains already data. The data should be pasted right below the last A row with data
In sheet 2, column A contains the following formula
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
I have a worksheet with 14 columns. Column 12 is either blank or when I finish that project, from a drop down menu I pick Yes. What I am hoping for is that as soon as I hit yes the line will be cut from the first sheet labeled Open and pasted to the next available row on the second sheet labed Closed. On the first sheet that blank row is eliminated so there are no spaces and on sheet two it always looks for the next available row as to not over write any data.
Also, to make sure I am doing this right too, I would right click the tab then click view code, paste it into the window (below the drop down that says (General) click file save workbook then file close and return to excel correct? One last thing, while looking at this page it shows my worksheet I call Open is really sheet 16 and closed is really sheet 17.
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?
I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc. I'm not sure if this is the way to go
Sub CopyRows() Worksheets("sheet1").Select FinalRow = Range("M65536").End(xlUp).Row ' Find the last row of data ' Loop through each row For x = 2 To FinalRow ' Decide if to copy based on column M ThisValue = Range("M" & x).Value ' could be any value If ThisValue = "yes" Then Worksheets("sheet2").Range("A" & x & ":L" & x).Copy Worksheets("sheet1").Select NextRow = Range("A65536").End(xlUp).Row + 1 Range("A" & NextRow).Select ActiveSheet.PasteSpecial = xlValues Worksheets("sheet1").Select End If Next x End Sub
I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions. I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).
I have used Month(Now()) to generate the current month value in cell B1. I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.
I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.
I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1 2 JLKNSTTP 3 85934942 4 5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
