VBA - Copy Multiple Cells If Specific Text In Row Then Modify
Mar 7, 2012
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.
View 2 Replies
ADVERTISEMENT
Nov 8, 2008
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 Related
Mar 21, 2013
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro.
FinPro
Sup
Veh
1Dn
2Cn
2Dn
Bio
Injec
[code]....
View 5 Replies
View Related
Nov 8, 2013
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
View 7 Replies
View Related
Dec 30, 2011
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
View 9 Replies
View Related
Feb 1, 2014
I have two separate data files that I am trying to combine into one worksheet. One set of data looks something like this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date2 Name1
Date2 Name2
Date2 Name3
This is repeated for something like 200 dates, and there could be anything from 1 to 10 lines for each date, for a total of over 1000 rows.
The other data file has this information:
Column A Column B ColumnC
Date1 Place1
Date2 Place2
Date3 Place3
I.e. one line for each date.
I want to combine the data so that Column C contains the Place data. All I could think to do was combine them and then sort by Column A, so I ended up with this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date1 Place1
Date2 Name1
Date2 Name2
Date2 Name3
Date2 Place2
And then I would just copy and paste to end up with this:
Column A Column B ColumnC
Date1 Name1 Place1
Date1 Name2 Place1
Date1 Name3 Place1
Date1 Place1
Date2 Name1 Place2
Date2 Name2 Place2
Date2 Name3 Place2
Date2 Place2
Ultimately I want to sort the combined data by Column C, I just have to get the data in there first.
Is there a formula or macro or something I could use to copy the Place names automatically? Each worksheet with 1000 lines and several dozen worksheets means I'd have to copy and paste dozens of thousands of times, which is a bit tedious.
View 3 Replies
View Related
Nov 7, 2008
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
View 9 Replies
View Related
Jan 17, 2013
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...
View 9 Replies
View Related
May 1, 2014
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.
View 11 Replies
View Related
Sep 11, 2012
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
View 3 Replies
View Related
Jan 27, 2014
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
View 2 Replies
View Related
Jan 27, 2014
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] .....
View 2 Replies
View Related
Apr 7, 2009
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.
View 7 Replies
View Related
Jul 12, 2007
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"
View 9 Replies
View Related
Aug 29, 2007
I 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.
View 7 Replies
View Related
Apr 16, 2008
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
View 9 Replies
View Related
Apr 7, 2009
Thanks everyone that has helped me with this so far. I am very new to VB so I definitely appreciate all the help.
I have the following code to select rows that meet 2 different criteria and copy them to a new sheet.
The problem is I only want to copy the values not all the formulas - the formulas don't work in the new location as they are referencing cells that are no longer there.
View 6 Replies
View Related
Apr 28, 2008
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
View 9 Replies
View Related
Nov 14, 2008
Objective: use VB to copy the first sheet (tab) from every Excel file in a specific folder to a new sheet/tab (for each) in a master spreadsheet. I will then aggregate this data into a summary table.
Following the advice of an old thread (Access: VBA combining multiple excel files to 1 new sheet), one time for each file in the folder. So with three excel files in the folder, I get 3 new tabs in my master spreadsheet but all are copies of the first tab in the master spreadsheet.
I'm wondering if maybe the code, which was in a post from 5 years ago, needs to be "modernized" for Excel 2007. Thanks for any help.
This is the code I'm using:
Sub Combine()
Fpath = "C: emp2" ' change to suit your directory
Fname = Dir(FilePth & "*.xls")
Do While Fname <> ""
Workbooks.Open Fpath & Fname
Sheets(1).Copy After:=Workbooks("Master.xlsm").Sheets(Workbooks("Master.xlsm").Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop
End Sub
View 9 Replies
View Related
Jun 30, 2006
I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:
MZ03948 134 126.23
PZ34533 128 12.28
IM04985 630 198.50
Z109384 40 35.65
2130494 76 12.28
2236326 12 45.63
5123132 12 12.23
Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:
Esky - "MZ" or "51"
Indy - "IZ" or "Z" or "21"
Gfld - "PZ" or "22"
Other - Anything else
View 9 Replies
View Related
Apr 29, 2009
how to add in a text box prompt that will modify code?
View 4 Replies
View Related
Nov 21, 2013
I have the following code that rearranges the data based on the values in the first column (cannot post attachments). Currently, the code is grabbing the whole columns when performing cut and paste and with larger sets of data I am getting "Excel cannot complete this task with available resources" error. Need to modifying the code so it only grabs the used range as opposed to the whole columns.
Code:
Sub ArrangingColumnsByNumber()
Dim Count As Long
Dim CurrentNumber As Long
Dim CurrentColumn As Long
Count = 1
CurrentColumn = 1
CurrentNumber = Cells(Count, CurrentColumn)
[code].....
View 4 Replies
View Related
Sep 9, 2013
I would like to know if there is a way to modify multiple excel files in a folder. What I need to do is hide various columns, adjust margins, cut and paste certain columns to a different position, and change the page layout. Is all of this possible, or will I need to change each individually? If you need more information, let me know.
View 5 Replies
View Related
Feb 16, 2010
I've been successfully using a for loop method to make minor changes to formulas or formatting in multiple sheets within the same workbook.
I would like to update a standard button I have in each sheet. This button jumps the user to the 'dashboard' sheet.
Now I want to add a functionality to the button which makes it input the refrence sheet name into a cell in the 'dashboard' sheet. Is there a way I can make a VBA macro to edit the code in multiple sheets? I'd love to not do this manually for 100 sheets!
Example of my for loop:
View 6 Replies
View Related
Sep 25, 2013
If I'm using this function to find the specific text "EXPIRED" in the specified cells, how can I add additional cell ranges on the same sheet to this function?
D17:D34 as well as F17:F34
=IF(ISERROR(MATCH("EXPIRED",Homepage!D17:D34,FALSE)),?"OK","ALERTS")
View 2 Replies
View Related
Aug 13, 2008
I have many text files ~5000 and I am searching for a macro that can import multiple files and also search the text file only importing two specific rows (error files may will not have 42 rows) while listing the file name in an adjacent cell.
So in column A I would like the file name, in column B any data in line 42 of the text file and in column C any data in line 43 of the text file.
Here is an example of the data in rows 42 & 43:
11 Waratah Street Mona Vale(2103) - Australie
-33.68 (-33°40') | 151.30 (151°18')
So far I have found the code below that will import all text files into one sheet but it does not satisfy my requirements.
View 9 Replies
View Related
Nov 13, 2013
I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.
View 4 Replies
View Related
Jul 3, 2008
I want to link him on all the spreadsheets, but his cell location is always changing because new doctors are being added every year. Is there anyway to link the specialty column with his name so that whenever his name comes up on the other worksheets, the specialty will be automatically filled in with the correct specialty value? I want to be able to do that with all the doctors.
Also, I want to highlight certain doctors in all the seperate sheets, I have Excel 2007 on my home PC, but on my work PC I have Excel 2002. To do this in Excel 07 I would just conditionally format the workbook to highlight certain names, but I cant seem to find a way to do this in Excel 02.
View 9 Replies
View Related
Jul 23, 2007
I'm creating a simple program that copy one or more specific cell values and place it on a specific cell in another sheet using loop to make it easier... I'm having a difficult time trying to figure it out..
Example
from sheet1 A1:A5
Sheet1
A | B | C
1 P45
2 P46
3 P47
4 P48
5 P49
and place P45, P47 and P48 on another cell, to be specific in C1,C2 and C3, in a different sheet
Sheet2
A | B | C
1 P45
2 P47
3 P48
4
View 4 Replies
View Related
Aug 21, 2014
i need a code line to modify in the code that when find duplicated string (found in 2 sheets) Copy to a next empty Column of a 3rd sheet
how code work: The code is working Fine (excellent!), Its compare 2 given range from 2 sheets( "sheet-1" and "Page-1") and if duplicated string is found on such Ranges, with condition of a min/Max of 5 to 12, then these string are copied on a new sheet called "Intercepted". The problem (is my mistake ) is that the small report is copied, BUT is not copy continuously to next empty column.
I tried to modify my self but not success because i don't know which line delete and where to add the the new code Line.
View 3 Replies
View Related