I wonder if someone has a clever solution for the following, I have a spreadsheet where I have done subtotal on some of the figures, does anyone know how I can copy (and paste into a new sheet)only the rows where I have a subtotal?
View 8 RepliesI have several macros that join spreadsheets and then sort and subtotal them. I now need to use my spreadsheet that has the subtotals and place only the subtotals into a new workbook. For instance the new file should contain the name of the item and "subtotal" for that item, and the list of all the data should remain in the original file.
View 9 Replies View RelatedExcel 2010 windows 8
I am in need of a formula to count filtered results. On Sheet1 (Job Flow) the user enters data continually . The most important is the Date in column D some measurements in columns N,O & P that gives a result as a code, example s15020 or c3005 in column Q. A vlookup is then pulls up a price for the code.
On sheet two Column A There is a list of all the codes. I need a formula that will give a count in column B for the amount of codes that was entered in to the data base "sold" for a date filtered time period, be it a week, month or year. In other words the codes in ,column A sheet 2, should reflect the amount of units for the time filter on page1.
I've got a bizarre problem here. I've been working on a piece of code and one line has started behaving erratically: Sheets("BLANK SCORE").Copy after:=Worksheets(Worksheets.Count)
I want to do is create a copy of the sheet called "BLANK SCORE" and copy it after the last sheet in the workbook. This code is part of a loop that runs a maximum of 10 times (potentially creating 10 copies) and I've had inconsistant results such as:
*) Code works and copied sheet is created (desired result)
*) Code is ignored and no copied sheet is created (shouldn't do that)
*) Sheet is copied to a new blank Workbook (...huh ?)
I've also tried changing:.........
I have a sheet that I need to routinely filter for a specific code then paste it into a different sheet in the same workbook. I would love to set up a simple macro that would do this for me, but I can't seem to figure it out.
In the results I would like the header row if at all possible, but I can always just make it part of the macro.
I am having trouble copying and pasting the results of a calculation on one sheet to another sheet. I believe my problem is that my destination cell is actually two cells merged so the destination is obviously not the same size as the source. Here is the section of code I am working on:
Code:
Sub CalculateDeviceFailureTimes()
'
' Used to manipulate the UEM data file to find the outage times of different devices in the Astro System
'
Dim Output As Integer
[Code] .......
Like I said the section after the initial 'If' works and 0:00:00 is entered into the cell D15 with no issue. My problem comes when I try to copy the result of the formula in 'G3' to 'D15'. Like I said I believe my problem is that 'G3' is a single cell and 'D15' is two cells merged..
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
Sheets("CIT Results").Select
Sheets("Open Calls").Range("A1:I" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Open Calls").Range("N5:V8"), CopyToRange:=Range("Q50"), Unique:=False
Moderators, can you please edit the Thread title. It should be "Advance Filter From and To Non Active Sheet"
I have a workbook with 50+ sheets. Within each sheet are rows of data in column A that I'd like to search for specific text.
I'd like to search each row from every sheet for specific words (e.g. "7 days" AND "Monday" AND "Tuesday" etc.) then copy the entire row containing all my search text in a new sheet on column A along with the name of the sheet it was found in in column B and the row number it came from in column C.
What I am trying to accomplish is to search through all the sheets and post results in new sheets for each search string.
I have a report that is run weekly that shows items that have been returned over the last 3 months. The report shows the original date of purchase and the return date, but not the number of days since the purchase and the return. I need to have any items that were returned over 15 days go to a new sheet and display just those rows of information.
View 8 Replies View RelatedI have a worksheet that contains 3 columns, A, B, C, that I need to run through auto-filter and copy the results from a cell, F2, into another sheet each time the filter criteria changes.
Although the worksheet will contain over 11,000 rows (the attached sample file is trimmed down to around 1000 rows),
Col A will only have 8 different possible criteria for autofilter: 1,2,3,4,5,6,9,10
Col B has around 70 criteria, and Col C has around 700 criteria.
The number of rows in the sheet and consequently the auto-filter criteria will likely change each time (but will usually hover around these quantities).
As an example, here is how I would envision this working for Col C:
1. Starting on the 1st Sheet (named "FW15"), I auto-filter Col C on criteria/value 1
2. I copy the resulting value from Cell F2 of sheet FW15 and paste it into the first empty cell of Col C in Sheet 2 (named "CopiedResults")
3. I return to my first sheet, FW15, turn off the enabled filter for criteria/value, and turn on the next autofilter Criteria/Value of 2
4. Repeat Step 2
.
.
.
Keep looping through Col C to make sure that all auto-filter values have been applied, and all resulting values contained in Cell F2 are copied over to the second sheet.
Likewise, I would need to run through the auto-filter criteria in Col A and Col B, and copy their resulting values (from cell F2) into Sheet2 Col A and Col B.
Attached workbook : autofiltercriteria3.xlsx
i am trying to do the following- can some one pleeeeeeease help me
Each tab has columns up to M.
1. Sum Column E for each tab(note column will varie in size)
2. Then copy Column C and Column E to one tab (called " upload)
there will be 60 tabs - so am tying to loop through and peform a subtotal for column E then copy "C" and "E" to another tab called "upload" note - all tabs will be copies to the one tab called "uplod once subtotal is performed.
I have 4 coloumns: Barcode, Date, Qty, Intials. The products will be scanned and the above cells will be populated via a VBA code. What the problem is that in the Barcode column - a product may be scanned on several different days, each entry may have a different Qty value. So how can I autofilter to show the various barcodes and get the total value of Qty (subtotal) and then transfer this value into a new sheet showing the relevant barcode and subtotal Qty?
View 2 Replies View Relatedway to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies View Relatedif you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies View RelatedI have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
I 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 Relatedattached the file. Sheet1 & Sheet2 are the "Source" sheets and "Summary" sheet is the "Target" sheet.
1) Macro "TopParts1" filters the Top 80% (Column B) in Sheet1 and copies this to A1 in the "Summary" sheet.
2) Macro "TopParts2" filters the Top 80% (Column B) in Sheet2 and copies this to E1 in the "Summary" sheet.
What I am hoping for is to combine the macros 1) & 2) so that the Table as
shown in "Summary2" sheet is done in one instance. The highlighting is just for illustration. Presently I need to select sheet1 rum the 1st macro... select sheet2 run the 2nd macro. Not very efficient. One table for 1 macro would be excellent.
I have 4 source workbooks and a master workbook which is linked to the source workbooks, if the source workbooks have no value in specfic cells the master workbook which is linked to it shows a 0 (not a problem). Now if I want to copy only cells which have a value other than the 0 to the end of used rows on another sheet on the Master workbook using VB I run into a problem because it sees 0 as a value and copies as well.
The issue is how do I stop the 0 showing and not being seen as a value. This 0 shows regardless of format.
I have a "Log Sheet" with dates in one column and times in another column.
Currently I have a formula which matches the months of the year and totals the time during that month below is this formula. (This formula id on the same sheet.
=SUMPRODUCT(--(MONTH($A$5:$A$1426)=1),--(YEAR($A$5:$A$1426)=2008),$F$5:$F$1426)
Now I am trying to clean up the sheet and I want to move all the results onto another sheet Lets call it sheet 2 and sheet 1
sheet one will have the months and times.
sheet 2 i want the formula in each cell, andto place the result in each respecting cell/
This is what I came up with but why is it not working?
=SUMPRODUCT(--(MONTH(Sheet1!A:A)=11),--(YEAR(Sheet1!A:A)=2007),Sheet1!F:F)
I have to extract all the entries that have certain words.
The worksheet has 600 rows, setup like
Column A=unique document number (never repeats)
Column B=Document Titles (some repeats, though the documents that are the same are not titled the same, though they have certain keywords in like documents)
Column C=Document Types
I want to search Column B for all titles containing the word/text string WOR, and then copy the row of A, B, and C when WOR appears in Column B, and put it into another worksheet.
Now obviously, I can do a "Find All" for WOR on Column B, but I can't cut and paste the results into another worksheet.
I prefer a simple formula, cause my VB and Macro skills are pretty non exsistant, though I will learn if that is what it takes.
I have this macro which does a really nice random selection, the problem I have though is that it deletes everything except for the result set. How to adjust the macro in such a way that instead of deleting the the original data, it copies and pastes the result set to a new worksheet?
Code:
Sub RandomSelect()
Application.ScreenUpdating = False 'disable screen updating to avoid screen flashing
Application.DisplayAlerts = False
Dim wbk1 As Workbook, wbk2 As Workbook, lstRow As Long, wks As Worksheet, startCut As Integer, selNum As Integer, lstCol As String
Set wbk1 = ActiveWorkbook
Set wks = wbk1.ActiveSheet
[Code] ......
I would like if someone could tell me what formula to put in the cells in Column E so that I can copy it to all cells in Column E and get the right results.
Here are the results I need: ....
I have a large data set (from columns A - I, with over 10,000 rows) of information located on Sheet1 that I need to be able to go through to find the criteria (which is text and is located in column B) I'm looking for. I know how to write the VBA code to use Excel's AutoFilter option .... what I don't know is how can I can identify and copy the results the AutoFilter pulls up, from Sheet1 into another sheet because the data is on a number of different rows.
For example, I have to search column B three separate times for the following criteria:
1. xyz
2. acb
3. hij
this is what I have for the autofilter:
Range("A1:I1").AutoFilter Field:=3, Criteria1:="=xyz"
Today, I may find the "xyz" information on rows 6-150, 755-787, 1021, and 8524-8999, whereas tomorrow "xyz" may be on rows 51-101, 8547, and 9989-9991.
The error above comes up every time I copy filtered data to a new worksheet. It does its work but the said error comes up.
Sub AUTOFILTER_withouthead()
' AUTOFILTER_for_drop Macro
' Macro recorded 1/27/2008 by DD
Dim ws As Worksheet, wd As Variant
Set ws = Worksheets((Worksheets("Destination"). Cells(1, 6).Value))
Set wd = Worksheets("Destination").Range("A1:F65000") ...
I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
I have a workbook with multiple sheets , final sheet has a data of all sheets , in the first sheet i want to select what results to be shown in that final sheet ,, specially the week and the LDM
View 12 Replies View RelatedI have two spreadsheets. One is a listing of football scores from the previous week. The other is the schedule for that week. I would like to be able to populate one from the other.
Even if someone could point me in the direction of which functions to use that would be helpful.
Based on the data in the results spreadsheet in columns M, N and P ... I need to look in the scores spreadsheet and compare to columns A, F, H and I. I would need to retrieve columns G and J for their scores.
I've been looking at SEARCH, LEFT AND VLOOKUP so far!
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over