In the attached file, the "importer.xls" is the file that has the ADO code and range of area for the data to be pasted, while the "Data to be imported.xls" is the source data that i want to copy which is in range "B1:D5" from each sheet "nightshift" & "dayshift". If you run the code, if would only copy partial of the data, could anyone plz fix the problem for me
View 5 Replies
I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...
View 3 Replies View RelatedI used vlookup function in cell C3 to find ID of employees in Sheet 1 in source workbook and then, I imported the Sheet 1 in master workbook.
The vlookup didn't work in master workbook. The cell C3 in sheet 1 that I imported is blank.
The original got the ID in cell C3, then I don't see the value in cell C3 in master workbook.
I have a question in regards to excel. I have a set of data arranged as follows:
Date System Desc
3/7/14 IPE Alphamo
3/7/14 IPE Beta
3/7/14 i3 Gammana
7/7/14 ews Pisqrt
7/7/14 IPE Jaque
8/7/14 saa Mistiqe
And I need to put them into a calendar with the following format:
3/7/14 7/7/14 8/7/14
IPE Alphamo Jaque
IPE Beta
I3 Gammana
ews Pisqrt
saa Mistiqe
I have tried index and match, vlookup and etc but it didn't work.
I changed the code below to add an extra column(E) this worked. However it does not keep the formatting from the top row in column E, How can i change the code to column E to keep the top row formatting ??
Also i added a list to cell A2, but this moves down, I would like to have the selected data move down but the list stays in cell A2, Was this possible ?
I started playing around with the macros feature, and hit "record" but didn't save anything. Now, whenever I try to close my sheet, it gives the "save changes" prompt, even when nothing's changed. There are no saved macros to delete...no idea what I did or how to get rid of it. I tried copying the cells to a new worksheet, and the new one gives the save prompt as well.
View 9 Replies View RelatedI have a large database of customers with their contact details and address etc.
With the database the main thing we analyse is source of the customer and sales person it was given to.
I have separate sheets of which narrow the database down by source. Such as telephone lead, website lead etc.
At the moment i am currently entering the data into the main database sheet and then have to enter again on the lead source sheet.
Is there any formula for a sheet for example website sheet in which it will look up a column in the main database (lead source column) and pull everything single row of customer details that have came from the website.
I would ideally like this to just continuously do this every time i enter data into the main database it automatically gets added to its separate sheet.
I have a code that paste my data into a new row.
At the same row I have a column with formulas, and I would like the macro to copy that formula into same column in next row.
Code:
LstRow = Bilag1.Range("C65536").End(xlUp).Row + 1
Bilag1.Cells(LstRow, "B") = Brreg.Range("B4").Value
Bilag1.Cells(LstRow, "A") = Brreg.Range("B5").Value
Bilag1.Cells(LstRow, "C") = Userform1.txtProjectNo.Value
This is what I have, and my formula is in column "D".
I have this formula:
=IF(T8=2,"6",IF(T8=1,"3",IF(T8=0,"0")))+IF(T9=2,"4",IF(T9=1,"2",IF(T9=0,"0")))+
IF(T10=2,"6",IF(T10=1,"3",IF(T10=0,"0")))+IF(T11=2,"4",IF(T11=1,"2",IF(T11=0,"0")))
and when I copy it down, I would like all the t's replaced by u's (since the data is in columns)
I am having a problem with copying a formula and I know that there is at least one way in which to solve this. I have all my data in one row. A1 -C1 (for example) ... I am looking to reference these values to a column in another part of my spreadsheet, K10-30 (for example) How do I go about getting excel to let me do this? I know that if you want to do it opposite, like data in columns and copying to rows you would use =Indirect(Address(Column()-X,X) with the X's being numbers ... but I am not sure how to manage this in my scenario.
View 5 Replies View RelatedI am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies View RelatedI have problem to copy data from other worksheet. yes, i can do Vlookup or IF function, but it will copy only when key in the formula, and it is not practical to do that if the columns are more that 10.
Situation is like this:
Worksheet 1 - > A1 Customer Name, B1 Amount, C1 Details.
Worksheet 2 -> A1 Customer Name, B1 Completion, C1 Total Cost, D1 Amount, E1 Date, F1 Details
I want to create a formula which will copy automatically all the data 100% completed in Worksheet 2 into Worksheet 1. What i have in mind is using something like this for Worksheet 1:
=IFERROR(INDEX(Worksheet2!$A$2:$F$15,SMALL(IF(Worksheet2!Criteria=100%,ROW(Worksheet2!Criteria)-ROW(Worksheet2!$B$2)+1),ROWS(A$2:A2))),"")
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.
I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.
I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.
I have two sheets (sheet 1 and sheet 2). Sheet 2 has a range of data about employees. Column A contains a unique reference number with the rest of the row (Column B - Column X) containing corresponding data about that empoyee.
When I enter that unique reference number in Sheet 1, Column A, and matches the value in Sheet 2, Column A; I would like the rest of the corresponding row data (Column B - Column X) from Sheet 2 to populate 'automatically' (copied) in Sheet 1.
I'm trying to write a formula to take data from the same cell in each of 50+ sheets and put it into a summary sheet:
On each sheet i have the total for that sheet in a cell, H11
in the summary sheet i want to list these horizontally, so total of sheet 1 into the summary sheet in D5, sheet 2 in E5, sheet 3 in F5 and so on...
Is there a way to write and copy a formula or will I have to manually amend the formula for each sheet ref?
I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
Excell file eWorked Example.xlsx
I am trying to set-up a formula that needs to be copied down and across and draws upon a data table that has a horizontal and vertical parameter that needs to be checked for the formula to return the right answer.
I have tried all manner of Index/Match, Offset etc but can only get one of either the vertical, or horizontal parameter working.
I have attempted a formula which is
=INDEX('PIP Input'!$C$17:$C$29,MATCH(H$1,('PIP Input'!$H$17:$H$29),0),MATCH($B4,'PIP Input'!$H$15:$L$15,0))
('PIP Input'!$H$17:$H$29) is column dependent on MATCH($B4,'PIP Input'!$H$15:$L$15,0)
If $B4 returned a different result from ('PIP Input'!$H$17:$H$29) then it would need to be ('PIP Input'!$I$17:$I$29)
How to nest the Horizontal Match inside the Vertical Match, such that the Vertical Match changes column depending on $B4?
I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.
In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.
I am trying to find a way to copy and paste multiple non concurrent rows of data from one spreadsheet to another.
Ex: I have a large worksheet with approx 20,000 rows of data. I need to copy
and paste every 100th row to a new worksheet.
I think this might be possible by setting up a formula and linking worksheets,
but I'm not exactly sure how to do it.
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
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.
I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.
I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
I use a macro to fill data from different files (in the same folder) to a master sheet. One particular cell (C16) of the files contains a formula for calculating Young's modulus (E) based on temperature (entered in cell C14). The same cell in all files has the same formula but since the calculations are based on temperature the value changes. In once case the formula is like:
View 2 Replies View RelatedI have searched and unable to find how to keep a formula as a formula in a cell so I can copy it to other cells. Every time I enter a formula, example: =if(a2>0,trim(v2)&"."&trim(x2),"") the cell immediately displays the value and I am unable to copy the formula to other cells because I get the same value all the way down the sheet. Example star.plus, star.plus, star.plus... Every cell in the range should have a different value and change whenever the cells in column v or x change. I also tried entering this formula on another workbook which displays correctly the formula in a cell and then copying and attempting to paste onto the existing worksheet but the past and paste special are greyed out.
View 3 Replies View RelatedI have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
The workaround:
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies View RelatedWe have folders of daily cash collections stored in such a manner, yearmonth. In every month, we will have worksheets sent by the end user to the finance dept, naming it using mmdd. (The folder in the drive will reads: C:Daily Cash Collection2013), (C:Daily Cash Collection2013 0104.xlxs), (C:Daily Cash Collection2013 0115.xlxs). I intend to put the master list outside the year folder, meaning, in the Daily Cash Collection folder (C:Daily Cash CollectionDCC_2013.xlsm). When I have a new folder for year 2014, my master list will be here (C:Daily Cash CollectionDCC_2014.xlsm)
I am looking to automate this opening of all the daily worksheets, select all data except the header row, and copy it into a master list (which will be data for the whole year, with 3 months of the previous year data).
The data in the daily sheets, it will have collections of the same Debit Note number from the file sent earlier. Meaning, if the file was sent on 0104, there is a DN0114-0002, collection of $50. In another daily sheets 0115, it will also have a collection of DN0114-0002 of $20. This 2nd information of $20 will also need to be captured as the payment in 0301 is partial and incomplete.
I will need to copy the daily sheets into the master list every now and then. Is there a way to check and copy the daily sheets and not repeating it and missed out one?
In another words, if I had already copied Jan sheets into the master list, will it look for the next worksheet that I had not copy and copy according the DN number? (it will be in running number but sometimes will have DN of the previous month due to the partial payment ealier).
Alternatively, if this is too complicated, how to insert a macro to copy all cells except the header (will be in fixed column and the first row will be fixed) from an open daily sheet, find the last row in the master list and copy it to the master list, and after copying, unclear the selection of the daily sheet and close the daily? Where can I put this macro as the daily sheet is from the end user. I can only put it into my master list, but my problem is, the file name of the daily sheet is not fixed, it depends on the day the end user saved and email the data to Finance Dept.
DCC foler.jpg
I am trying to create a macro to copy a set of datas in a row , paste into another workbook (specific sheet),then save it as one of the datas that was copied over.
Workbook1
Ticket#
Description
Name
12345
Test 1
David
32145
Test 2
Steve
Workbook2 - sheet (ABC)
Copy row 2 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)12345
Test 1
David
Saveas Workbook2 - ABC - "12345" - Ticket #
Copy row 3 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)
And so on. I will have a set of data to trasfer to Workbook2, each row different workbook.
