Column C = Project numbers with a validation list to choose from ("PN1, PN2, PN3, PN4")
Column D = Expense Category with a validation list to choose from ("ODC, TVL, MAT")
Column E = The account number that is associated with each specific project number/expense.
Now for several projects, the ODC and MAT go on the same account number. But on another ODC and MAT have their own account numbers.
For Example,
If PN1 / ODC then E = 1.001
If PN1 / MAT then E = 1.001
If PN1 / TVL then E = 1.002
If PN2 / ODC then E = 2.001
If PN2 / MAT then E = 2.001
If PN2 / Mat then E = 2.002
If PN3 / ODC then E = 3.001
If PN3 / MAT then E = 3.002
If PN3 / TVL then E = 3.003
The range of the rows are 4 thru 500.
Is there a way to write a macro to automatically populate the account number in Column E based on the criteria selected in columns C and D?
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
I am trying to modify the macro listed below for the following example. It would work when I have only numerals in the cell but this new query, the cell has both letters and numbers.
I want to compare column "M" from worksheet one to column "B" in worksheet two. If the information matches, then copy the value adjacent from worksheet two column "A" to worksheet one column "L".
Here is the macro that worked for me using a search of only numerals.
figuring out why the data doesn't copy?
Macro:
Sub merge_accession_PS_rad_productivity() Dim rng2 As Range, c2 As Range, cfind As Range Dim x, y With Worksheets("Imaging_Summary") 'N4=Accession on Imaging Summary worksheet
What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
I have an order form created in excel with a list of about 1600 products. I have a column set up for the customer to place the ordered amount of each product. What I need now is a way to transfer only the rows that have a value in the "ordered" column to a new sheet. I have seen it before , but don't know how it was accomplished.
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
Now i have one excel sheet with two separet sheets in it ( Sheet 1 ) and ( Sheet 2 ) . I have in sheet 1 a column A with material codes and ColB is discreption and Col C IS Prices . But in Sheet 2 Col D is materila Code and Col I is a price .
the recordes in sheet 1 are around 11000 but in sheet 2 are around 2200 where the sheet 2 has a specific materials from sheet 1 .
Now i want to update the prices in sheet 2 from sheet 1 for each item after confirm that the materila code in sheet 2 equal in sheet 1 so copy the price from sheet 1 to sheet 2
Sheet 2 is old prices and sheet 1 has new prices so i need update the new prices in sheet 2
I have a worksheet with data in columns AW to columns QZ. I want to be able to copy a range of columns into another worksheet. The Columns AW to QZ have the same headings repeated every 10 columns. e.g. English, Maths, Science, Art & Design, Humanities, PSHE, History, ICT, RE and Attendance. Each block represents a different time period.
I want to be able to copy all the columns headed one subject (e.g. English) into another workbook. I used the following code to open the worksheet and paste the data into it, but there are so many columns for the 10 subjects that the code is too big to run so I need to shorten it and i don't know how to.
Sheets("On Roll Data & Levels").Select Range("AF2:AF99").Select 'KS2 English column Application.CutCopyMode = False
I have one worksheet from which i need to copy only few columns (Ex column A,B, D,G...need not be in sequence.....) to a new sheet.can you please help me to write a macro for this.In addition: Do we need to specify the number of rows in column or is there any way to get data till the end of column automatically.
i need to match 2 columns on one worksheet with 2 columns on another.
One column is alphanumerical (Reference Numbers), the other is company names.
I need to ensure that both the Reference and Company name match from one sheet, with the company and reference from another.
e.g if A1 and B1 on Wks 1 = A1 and B1 on Wks 2 = TRUE, anything else is FALSE.
There is likely to be Reference number and Comapny name duplicates, therefore the trick is to ensure that the number of duplicates match?(I.E ABC Company, Reference number 1234 may appear 5 times on worksheet 1, however if it is only on Worksheet 2 4 times, then this must be flagged).
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next Application.EnableEvents = False
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.
The workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.
I have copied this sheet to a new sheet in the same workbook:
If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.
FYI I am using Excel 2007 with a 2003 compatability worksheet
A copy of the workbook is attached. Any idea what’s happening?
how to filter rows AND columns in a worksheet. I filtered the one column in the spreadsheet (the main "yes, no, or n/A" for each row). Now I want to be able to filter each row. For example, if the column response is "yes" (most are), I want to filter each row to the "yes's, no's, or n/a's" for each column in that row (it's pretty long, from C to KS). I have attached a sample worksheet (the one I am working on minus confidential information).
Basically - I need to filter by a column and THEN, filter each row by the Yes's, No's or NA's in the columns associated with those rows.
I have a problem in my list cheking. I have two sheets in my attached excel file. I have mordified for my own job security purpose. I need to check the individual cells of "parent" column in "Missing Asset" with "parent" column of "Asset" worksheet. It means, presence of cell text of A2 of "Missing Asst" sheet has to be checked with "A" Column of "Asset" sheet. If A2 text is present in "A" Column of "asset" sheet then "Y" should appear in B2 cell of "Missing Asset" sheet. Else "N". Its just Column text checking wothin two work sheet & then identify the cell which is not match. I guess "VLOOK" can work. Is it not?
I am OK to set values to the third row of "Loop Folder" from a column in " T" but I haven't figured out how to capture the remaining 22 columns of this worksheet.
I have a workbook with 20 worksheets. I want to start from any worksheet and run a macro that will copy some columns (A:E) from another worksheet called TABLE1 into columns I:M of worksheet I was originally in. I don't want to specify which worksheet I am in when I start as they are all called different names and I want the generic macro to run from any sheet. I simply want the macro to register the worksheet I am in when I run the macro as the reference sheet, and copy columns/data from TABLE1 into the correct columns of the worksheet.
I would then select the next worksheet - run the macro again - and it would copy the same columns from TABLE1 into the worksheet I am in. I know the following is not right, but what I have so far is =
this successfully goes to TABLE1 sheet and copies columns A:E but does not go back to the sheet I was originally in - I don't know how to code so it knows what the starting sheet was - it tends to either fail or copy the columns back into TABLE1 sheet.
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues
One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.
There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.
ive done basic c++ programing years ago, but know nothing about vba or excel macros.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
