I have a huge data set as given follow and want to Copy a the value of treatment from the cell having Treatment description at some other sheet through VBA
'UPDATE [XYZ 1213]
SET TreatmentDescription = '000'
WHERE ( ("Abc" < 00) );'
'UPDATE [XYZ 1111]
SET TreatmentDescription = '093'
WHERE ( ("wer" < 00) );'
Like in upper code I want the output sheet 2 as Cell A1 = '000' and A2 = '093' and so on
I have some specific procucts in coloum A in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 1
LSearchRow = 1
[Code] ....
Err_Execute:
MsgBox "An error occurred."
End Sub
I have some specific procucts in coloum A(a2, a3, a4, till a50) in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
[Code] .....
I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Useful information are included !!!
I'll give an example of a record...
Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
how to work macros or VBA
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go
I am trying to write a macro to search a column for a specific text string which when found, will copy the whole row the string is in. Once this row has been copied, I then want the macro to activate a new sheet and search for the next available empty row to paste the data. Once this has been done, go back to the original sheet and find the next cell in the original column with the specified text string and repeat until the range has been satisfied. Below is the script I have that sort of works.
View 5 Replies View RelatedSo I have a column that will have the same text in all of the cells contained within it except for one cell. Is there a formula that I can use that will automatically find and transpose the text of the one cell that is unique out of the column? I was thinking of an IF function, but the column could be up to 60 cells in length.
View 1 Replies View RelatedI have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers. What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.
Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need. Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.
I'm trying to copy data from one excel sheet to another excel sheet. However, the data to be copied is dependent on the 'client name'.
To explain this further, in the first list I have a detailed report on our clients and the services provided to every employee of that company/client.
However, the sheet two only needs the names of the employees that belong to a specific client.
This can be done manually by setting a fliter on the name of the client/company, but I need to be automated. To ensure only that specific company/client company's employee name is copied.
I've searched for a solution on the board and the internet too but to no avail; I am trying to create a macro that will select the entire column based on a cell reference G1 which is infact a Date.
Column G to Column S are pre-defined with a specific Date in row 8. If the Date in one of these columns (G8 to S8) match with the Date in cell G1, say it finds a match with H8, then I want to copy the range F14:F2000 and paste as values in range H14:H2000. The possibility of a match found is limited to a single columns from G to S.
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.
View 7 Replies View RelatedI am using the code below in Excel 2013.
Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
View 4 Replies View RelatedI'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedI have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub
I need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
a1=aaa
a2=111
a3=222
a4=333
a5=bbb
a6=444
a7=555
a8=ccc
a9=909
a10=234
if a1 = aaa then copy a1 to b1 until the cell =bbb
if a5= bbb then copy a5 to c1 until the cell =ccc
if a8= ccc then copy a8 to d1 until the cell = ddd.........
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
excel.jpg‎
I want to do the following:
If cells in column B from Sheet 1 contains ''text'',
Copy Cells from column D and F from Sheet 1 - TO - Sheet 2 in columns C and E -
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
What I'd like to do is copy text down in column A but when a cell in column C says something like 'end of data' I'd like the copy to pick up the next row in Column A and copy down until 'end of data' and so on.
Or maybe copy text down in Column A until the text changes in col A.
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:
I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"
The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working.
Select Case oColHeader
Case InStr(oColHeader, "how much", 1) > 0
Selection.EntireColumn.Copy
Case InStr(oColHeader, "level of sat", 1) > 0
Selection.EntireColumn.Copy
End Select
I want some VBA code that will:
1) automatically check the active row to see if it contains specific text (i dont' care if there is other stuff in the cell or cells), such as "truck 1 blahblahblah" and checks for specific font color, such as green.
2) if the row does, then I want it to copy ALL the cells in the active row EXCEPT Column A (which contains the date), and then paste them twice, one 21 rows down from the active row, and one 35 rows down from the active row.
3) Once copied and pasted, modify the pasted cells slightly. The first pasted cells need to say Truck 2 blahblahblah in Column C and be in blue font (instead of the original Truck 1 blahblahblah in Green Font), the second needs to say Truck 3 blahblahblah and be in yellow font. Everything else that was pasted will be the same EXCEPT they will be in blue or yellow font.
i am trying to figure out a vba code to select the last cell in column C that contains "Cgy".
View 3 Replies View RelatedI need to find a word in a column, ie. "JAN" in col D (only first found is enough, not all instances) and then display message that "January has been updated", if not found, display message "this month has not been updated". The word "Jan" will be stored in a cell, so I need the code that could pick up the contents of that cell and lookup the col D (in another sheet, ie. "MthMaster") for that word.
That does allow me to filter the output in Master, but ideally what I would want to do is not have to set aside 300 lines in the Master file for each of the Staff files. Conceptually, I'd like to have the spreadsheets Staff A, Staff B, etc. look for and export only lines which have data in them and then have Master bring those lines in automatically. This may not be possible, in which case your suggested solution is the best approach.
View 9 Replies View RelatedI have dates in Column I (for the whole year) and i would like to paste them into specific cells in three different Columns A,B,C (ie A2, A5, A8, A11 etc) Same Numbers different letter for each respective column. I can get it to go from one cell to another but when i try to specify specific cells
This is what i have so far:
Sub Test()
Range("I1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
End Sub