If Then Copy Multiple Cells
Nov 17, 2009
My basic if then:
=IF(AC4=$I$29,P4:Y4)
What I want this to do is copy the range P4:Y4 if AC4=I29
All the solutions to I have found refer to returning a single cell for a true result, without using vba I cannot find a solution to return a range of cells or multiple non-adjacent cells in the same row.
I initially tried an advanced filter copied to a new column, but this just gave me a list of true results, but this needed to be matched to other data in the row.
Here is a subset of data so you have a better idea what I am trying to do.
I need to create a list of tasks with the comment "RAS SENT".
The list needs to include Task NO., AIRDOC No. and the dates and times.
[IMG]file:///C:/DOCUME%7E1/moinn/LOCALS%7E1/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/DOCUME%7E1/moinn/LOCALS%7E1/Temp/moz-screenshot-1.png[/IMG] TASK No. TASK STATUS AIRDOC No. RMT No. MSN DUE DATE DUE TIME (GMT) DELIVERY DATE DELIVERY TIME (GMT) COMMENTS SA106 Completed 10586 70524033 217 06-Jul-09 4:00 PM 06-Jul-09 3:57 PM INITIAL RESPONSE SA107 Completed 10587 70523993 554 06-Jul-09 4:00 PM 06-Jul-09 2:58 PM INITIAL RESPONSE SA112 Completed 10603 70523994 1144 07-Jul-09 4:00 PM 07-Jul-09 4:00 PM INITIAL RESPONSE / SCRAPPED PART SA059 Completed 10093 70522122 626 08-Jul-09 4:00 PM 08-Jul-09 2:53 PM RAS SENT SA108 Completed 10610 70524081 758 08-Jul-09 4:00 PM 08-Jul-09 3:54 PM INITIAL RESPONSE SA109 Completed 10606 70523996 220 08-Jul-09 3:00 PM 08-Jul-09 2:55 PM INITIAL RESPONSE SA107 Completed 10587 70523993 554 10-Jul-09 5:00 PM 10-Jul-09 11:34 AM RAS SENT SA110 Completed 10619 70524131 325 10-Jul-09 7:00 PM 10-Jul-09 11:51 AM INITIAL RESPONSE SA110 Completed 10619 70524131 325 11-Jul-09 2:00 PM 11-Jul-09 10:19 AM RAS SENT
View 9 Replies
ADVERTISEMENT
Feb 11, 2009
I have a vlookup formula that I want to copy from cell b16 to b30. How can I do that without excel adjusting all of the formula. The formula looks like this: =vlookup(b16,b1:b15,1,false). I want b16 to change with the cells but "b1:b15" I don't want to change. it should look like this in the worksheet
cell b16 =vlookup(b16,b1:b15,1,false)
cell b17 =vlookup(b17,b1:b15,1,false)
cell b18 =vlookup(b18,b1:b15,1,false)
cell b19 =vlookup(b19,b1:b15,1,false)
cell b20 =vlookup(b20,b1:b15,1,false)
cell b21 =vlookup(b21,b1:b15,1,false)
View 2 Replies
View Related
Nov 7, 2009
I have the code below, which is auto-numbering in column B on sheet 2 (IR Register) and indexing down one row each time, ready for the next unique entry.
View 10 Replies
View Related
Aug 28, 2007
I am looking for a way to perform a find, and then copy multiple cells. The code below is what I have this far:
'The code under this section opens the first file and copies the Surrogate Compound components
Dim basebook As Workbook
Dim mybook As Workbook
Dim mybook2 As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim rnum2 As Long
Dim rnum3 As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FileName1 As Variant
Dim FileName2 As Variant
Dim FileName3 As Variant
The code above will actual perform the find, and copy what is in the criteria of the search. However, I am trying to find a way to perform the search, copy the data that is in the search creteria plus data that is in another cell. The data in the other cell will always be different, so I cannot use that in a find criteria. The other data will always be found in the "E" column. I was thinking that possible there might be a way to perform the find, copy that entire row, and then just delete the columns that are not needed. However, I have been unsuccessful in my attempts.
View 9 Replies
View Related
Mar 26, 2008
As an answer to a post about vba script for consolidating workbooks based upon pre-defined cells,
Sub ConsolidateDate()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim I As Long
Set wbDst = ThisWorkbook
Set wsDst = wbDst.ActiveSheet
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "C:Myfolder"
For I = 1 To .FoundFiles.Count
Set wbSrc = Workbooks.Open(.FoundFiles(I))
Set wsSrc = wbSrc.Worksheets("Data")
wsDst. Range("A" & I) = wsSrc.Range("A2")
wsDst.Range("B" & I) = wsSrc.Range("C6")
wsDst.Range("C" & I) = wsSrc.Range("D7")
wsDst.Range("D" & I) = Dir(.FoundFiles(I))
Next I
End With
End Sub
I was hoping someone could translate the meaning of this code for me, I am a beginner with using VBA and I am trying to write VBA script for a similar task I am working on. The references to cells A2, C6 and D7 were used as an example of cells to be copied.
View 2 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
Mar 26, 2009
I have the following VBA code in Excel that looks in sheet called mri.txt for a cell called “AcquisitionMatrix”, Then goes one cell down and two cell to left and copy them all into Application.Workbooks(imgMain).Worksheets("Sheet1").Activate cell “AC2”, But for some reason it copies the field’s name as well such as:
AcquisitionMatrix , 0, , 256, , 256,.
Is there a way to avoid copying the filed name into cell AC2? Such as :
0, , 256, , 256,?
here is the
View 2 Replies
View Related
May 12, 2009
I have a spreadsheet that contains data that is currently split up into 2 or more cells. Sometimes it's 2 cells, sometimes 3, even 4, etc. I would like to right click on multiple adjacent cells, select copy, right click on another cell and paste these multiple cells into 1 cell. I checked paste special but didn't really find anything. I'm not sure a formula/macro would work either as I need to manually determine which cells need this attention.
View 2 Replies
View Related
Feb 20, 2012
I need to have cells from sheets to automatically transfer to a separate total sheet...
So in other words for every tab/sheet I have...I need cell B10, once populated, to automatically transfer to a designated cell on a separate Total tab/sheet...
I tried =Sheet2!B10...but when I copy it into the next cell it reads...=Sheet2!c10...what I need is for the sheet to change...but the cell to stay the same...
View 1 Replies
View Related
Mar 1, 2012
I have to take data from about 10 rows and then paste them into ONE cell . . . any easy way of doing that without VBA or concatenating?
Example -
Move -
Jeff
Bob
Joe
Mary
into Jeff Bob Joe Mary
View 1 Replies
View Related
Jul 22, 2012
I'm so proud of how i managed to get my first VBA project working. Yet there is one thing that's missing:
I would like to know how i can get the data entered in the cells to the left actively show in the textbox to the right.
Because i want to make a standard solution that can be copy pasted from the text field easily.
Screenshot of current VBA project.
View 6 Replies
View Related
Aug 22, 2012
I about 150 different workbooks that I need to copy the cell data from the first sheet to a second workbook
The code is running all the way through to the "Clear values?" pop-up box, BUT nothing is actually being pasted into my second workbook
Sub TransferData()
Dim wkb As Workbook, wks As Worksheet, LastRow As Long
Dim FilePath As String, FileName As String
Dim ws As Worksheet, blnOpened As Boolean
'Change these variables as desired...
FilePath = "C:UsersPipeline2DesktopOveralnd Focal Points" 'change path here
[code]....
View 2 Replies
View Related
Aug 28, 2007
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.
Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.
As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.
View 2 Replies
View Related
Jan 26, 2008
I have a worksheet, where i type in the id of a member and its uses vlookup to search that member, i enter all the other data required. I am designing a library system and need to record a loan. I have code off another thread that "Copy Cell On Each Change To Next Blank Cell In Column" The problem with this is that when i implement it in my system i change the id cell and that changes the member but when i changes the ranges in the code it doesn't recognise a change has been made, because of vloookup. Current code i have got for the page is
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$3" Then Exit Sub
Application.EnableEvents = False
Range("A65536").End(xlUp).Offset(1, 0).Value = Target.Value
Application.EnableEvents = True
End Sub
View 2 Replies
View Related
May 10, 2014
I would like to select say 2 id from sheet1 and 2 names from sheet 2 randomly and copy to sheet3, to cells a and b,have seen various codes but none seem to fit the bill.
View 3 Replies
View Related
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
View Related
Apr 5, 2007
I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.
I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A. I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact.
........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000
and then eventually like this:
. A ............. B ......
1234 .... 567890000
On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.
View 5 Replies
View Related
Feb 13, 2008
I need the macro that looks in to say Column H sheet1, if value ="true" then copy cells in column A, B, D, F to Sheet2.
View 2 Replies
View Related
May 8, 2008
I need to copy multiple cells from 1 worksheet to another worksheet on different workbook and for every entry it has to create a sequence #
- source file (ex. data1.xls, data2.xls, etc)
* data & field structure is fixed
- destination file (case1.xls)
* field structure is fixed
1) destination file (case1) will be opened first, a button is prepared (associated w/ macro) and it will open the source file (ex data1.xls) & then copy the cells B2,B3 and E2,E3
2) the copied cells will be pasted to destination file (case1) in cells B3,C3 and D3,E3 respectively
3) a sequence # will be created in cell A of the destination file
this process will be repeated to other remaining files (ex. data2.xls same structure as data1.xls) manually. It means i will perform the task only if required. i have a created a simple code attached to case1.xls
View 6 Replies
View Related
Jan 30, 2013
Recently ran into this problem where when I select(highlight) multiple cells, it automatically copies the highlighted cells.
[URL]
This auto-copy also occurs periodically when I select(highlight) a single cell.
Notes:
1. I do not have a copy hotkey on my mouse, as this also happen when I use the trackpad.
2. This only happens in excel. When I select multiple items on my desktop, it does not auto copy.
View 1 Replies
View Related
Feb 21, 2009
I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
View 13 Replies
View Related
Sep 20, 2012
how to loop through workbooks in a certain directory and copy the rows in sheet1 where column B contains numbers greater than zero, and then pasting them into a new master workbook. The sheets will be named differently each week but will always be in the same directory.
View 4 Replies
View Related
Feb 6, 2013
I have an attendance file w/least 30 sheets. 29 of the sheets are for each separate group. The other sheet is the main one where I need to collect information like "total participants in attendance" and "total members in group" Then I divide those two and get the percentage of attendance.
The sheets containing the individual groups info are set up like this... (1=they were in attendance)
GROUP 1
Name / January / February / March/
Jess________1________0________1
Ryan_______1________1________0
Joe ________1________0________0
----------------------------------
total P ____3________1_________1
members___3________3_________3
% _______100_______33________33
The main sheet looks like this
Group / Jan. Participation / Jan. Member total/
Grp 1 ________3_____________3
Grp 2 ________8_____________10
Grp 3 ________7_____________10
---------------------------------------------
_____________18____________23
% total-__________________78.2%
I know I can manually go through and link the sums of participation and total group size into the main sheet, but I have a lot of workgroups and need to do this every month, is there an easier way? I am willing to change the set up of the sheets.
View 2 Replies
View Related
Dec 19, 2013
I have tried to write the below VBA to copy a specific cell to a specific workbook. I have set the folder path in B1 and listed the file names in column E4 onwards. E1 being the number of files in column E. I get a run-time error 91 "Object variable or With block variable not set" on Current File = ActiveWorkbook.name.
Sub UpdateParameters()
Dim CurrentFile As Workbook
Dim wbOpen As Workbook
[Code]....
View 4 Replies
View Related
Apr 15, 2014
I am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
View 5 Replies
View Related
Apr 2, 2014
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
View 12 Replies
View Related
Jan 15, 2013
In Excel 2007, Windows 7 Home Premium, I am trying to summarise multiple worksheets into one sheet, creating a list in one column in this summary sheet that includes the cell contents from the same cell from each sheet. For example, my first sheet is called KCD183 and I want to list the value from KCD183 Cell A2 in my Summary Cell A2, then show KCD184 Cell A2 in Summary Cell A3 (i.e. the next row down). So my Summary sheet will list all cell A2s from all my sheets, 1 after the other down column A and will continue to add these for any new sheets I add.
I realise that I could just export the spreadsheet to Access and report on it from there, but I don't have the software!
View 4 Replies
View Related
Jan 15, 2009
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
View 13 Replies
View Related
Mar 14, 2013
On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
