I need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
I've got a code that does what I want it to do, but I have to select the start point manually becuase it's position varies on different documents. It's usually around row 650 out of 1200, but the cell I want to start the macro at always contains the word ELECTRONIC. What I would like to do is modify my code so that it finds that cell itself and then runs the rest of the code. I'm not very good with VBA, but I've tried a few things like Dim LR-2 and using that but I get error messages about duplicates.
Here's my code
HTML Sub Macro1() ' ' Macro1 Macro ' SALES ORDER FORMAT ' '
In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).
In column B, i have some other details and then so on and so forth.
In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.
I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.
For each row in Column C, if the text starts with "Calls from service no", it must extract the last word and paste it into corresponding row in Column A. So if the macro finds a cell in Column C starting with "Calls from service no..", then the last word (the last word will always be a number and is seperated with a space) of this cell must be pasted in cell A45. So it must continue for all occurances in Column C. Sample of text in cell = "Calls from service no 0431324345" and "Calls from service no 3242134254" etc.
I also require similar, but this time it must extract as shown below. If text is found in Column C starting with "Local" or starting with "Long Distance" or starting with "Mobile", only this text, ie "Local" or "Long Distance" or "Mobile" must be pasted in Column B.
I have sheet1 that contains the following information
Column A Column B company1 - book value 0,5 company1 - earnings 0.2 company1 - R&D 1111 company1 - total assets 200000 company2 - book value 2 company2 - earnings 333 company2 - total assets 12
So column B contains only numbers and note that for company 2 I do not have any numbers on R&D.
I have in sheet2 only once the company name. I would like now that if column a contains the text "book value" to have the value of column B pasted in sheet 2 column B. Same for earnings to be pasted in column C. R&D in column D and if R&D is not present for that company then the value should say something like unknown.
I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:
Current List ----> Column A -->Column B Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar
I am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance: On Tab 1, A2's value is , K2's value is [9.38]. On Tab 2, A70's value is , K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL] Second tab, store's stock: [URL]
Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.
Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.
What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.
Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:
Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]
I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)
VB: Dim ws, ws1 As WorkSheets Set ws = ThisWorkbook.Sheets("Data") Set ws1 = ThisWorkbook.Sheets("Target")
I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?
I have an Excel invoice set up and working well. It does a bunch of things with macros - e.g. on save it increments the invoice number well as creates a jpeg screenshot for the invoice archives. I have added an additional worksheet (titled 'VAT') to the workbook. The new 'VAT' worksheet has five simple columns; Invoice no, Subtotal, VAT, M.O.T. and Total.
What I need:- On saving the workbook I would like to add a macro function that copys the final contents of the Invoice no (H2), Subtotal (C37), VAT (C38), M.O.T. (F38) and Total (I38) cells from the 'Sales Invoice' worksheet to the newly created 'VAT' worksheet in the respective columns. I would like this to be cumulative, i.e. continue to add the contents of the afore mentioned cells to the appropriate columns in the 'VAT' worksheet every time the invoice is saved. I would also like to have the Subtotal, VAT, MOT and Total columns summed and outputted in a cell of their own - but hopefully I can handle that.
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
When Complete repeat until it reaches the end of column A
Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.
I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.
I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.
I have an excel spreadsheet with a column of part numbers. The part number consist of a prefix, base, and suffix which is separated with dashes. For example 1C3Z-6211-AA. I am wanting to copy the base number only to another column. The problem is the prefix can be 2 to 4 digits, the base can be 4 digits or longer and the suffix can be 1 to 4 digits long. Is there a way to copy only the number between the dashes?
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?