Keeping Only Selected Rows Over Multiple Worksheets
Nov 29, 2013
I have an excel file with over 20 worksheets and each of them have around 1200 rows. The first column in each worksheet contains the variable names and then the data associated to it is present horizontally.
I only want to keep around 80 rows from those 1200 rows. They are not in sequence (means they are not in continuous order) so I manually selected those rows by deleting the non required rows step by step.
I did it manually on 2 worksheets but I don't want to do that manually over 20 worksheets. Is there any method that can speedup the whole process.
I am attaching the snapshots of the worksheets..
This one is before I deleted the unwanted rows.
This one is after the deletion of unwanted rows.
View 3 Replies
ADVERTISEMENT
Jun 22, 2014
I have a workbook with over 70 tabs whose position shouldn't be changed. Some of these tabs are colored in yellow (sorting by tab color is not allowed). I need to select these yellow tabs first and loop through them (only yellow tabs) and hide empty rows in the range of A1: G 50 on each of them. Grouping sheets wwon't work because each tab has different last row with data within that range.
View 6 Replies
View Related
Jun 25, 2014
To this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..
Dim relativePath As String
Dim Selected As Long
For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
[Code] ..........
View 6 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Mar 30, 2007
I would like my cursor to stay in the same cell when I switch worksheets. If I'm in B40 on worksheet one, and switch to worksheet two I would like the cursor to be in B40.
I'm using very basic excel functions with no scripting at the moment.
View 9 Replies
View Related
Jul 12, 2008
The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.
View 13 Replies
View Related
Sep 25, 2008
I have a sub that will join the text together from multiple columns into one cell but it only works for one row. I would like it to work through the rows selected. How do I do this?
Sub JoinText()
myCol = Selection.Columns.Count
For i = 1 To myCol
ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
End Sub
View 9 Replies
View Related
Jul 14, 2009
I have around 150 excel files with sample data as follows in "sheet 1" of each workbook,
Excel doc 1:
ABC1
Column 1Column 221Data 1Data 132Data 2
Excel doc 2:
ABC1
Column 1Column 223Data 3Data 334Data 4
I want the rows with data in column 'B' and empty column 'C' from every sheet to be copied into 1 sheet.
Output to be as,
Final Excel doc:
ABC1
Column 1Column 222Data 2
34Data 4
I have a VBA code sample to select the required files in a folder and run the macro over it. The VBA is as follows,
Sub Importxlsrows()
'Import all selected rows to one sheet
Dim xlsDoc As Object
Dim xlsFileName As Variant
Dim RowNo As Integer 'row number in excel
Dim iRow As Long 'row index in Excel
'probably here we need to insert the required logic
End With
Set xlsDoc = Nothing
End If
Next i
ShowStatusFree
MsgBox "Required rows of selected files are imported into the sheet", vbInformation, "Done!"
End Sub
View 9 Replies
View Related
Apr 26, 2013
I have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
View 3 Replies
View Related
Mar 4, 2014
I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.
View 1 Replies
View Related
Dec 1, 2013
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
View 3 Replies
View Related
Feb 6, 2008
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
View 2 Replies
View Related
Mar 9, 2013
I have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?
[URL] ..........
View 4 Replies
View Related
Jul 11, 2012
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
View 8 Replies
View Related
Jul 23, 2014
I have a large data file with several different worksheets. In one cell I have a large set of individual numbers (i.e in the cell there is "123456 234234 939829") This value is my 'sample' number. The same sample could be in multiple rows on one sheet and could also be on other sheets. I am trying to find the sample in a column (on some sheets its column "Q", other sheets it could be different columns, but that column is fixed per sheet) and then copy the information on the same row in columns A & B and copy it to my target sheet. Then the next place the sample appears copy that info to the next row of my target sheet. So far I am stuck getting it to find the multiple values on one sheet. My code is below.
Code:
Sub Samplesearch()
Dim sample As String
Dim x As Integer
Dim y As Integer
Dim lr As Long
Dim lsr As Long
[Code]....
View 5 Replies
View Related
Sep 20, 2012
I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.
View 9 Replies
View Related
Aug 19, 2014
I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.
View 13 Replies
View Related
May 16, 2008
I have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?
View 9 Replies
View Related
May 23, 2013
I'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies
View Related
Feb 28, 2008
I have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...
Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State
etc...
It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).
I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).
Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.
I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.
View 9 Replies
View Related
Nov 20, 2012
I have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".
What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".
View 5 Replies
View Related
Jul 3, 2013
I worked on a workbook which has multiple worksheets( mine has 6). The data doesn't start from A1. I want to copy the data from each worksheet into a sheet called summary. I want to create the macro that would only copy the heading row once.
WB test.xlsxWB test.xlsx
View 5 Replies
View Related
Jul 30, 2014
I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.
Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows
In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.
=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI
[code]....
View 4 Replies
View Related
May 9, 2008
I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical.
Currently I have this thing solved with following formula:
N48=sumproduct(sumif(indirect("'"&$B$378:$B$385&"'!A:A");A48;indirect("'"&$B$378:$B$385&"'!L:L")))
Where B378:B385 includes sheet names.
But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it.
So, in addition of getting values for specific product number I need to get values from specific column based on column header.
View 9 Replies
View Related
Aug 15, 2014
I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.
Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.
View 2 Replies
View Related
Dec 31, 2012
I have a spreadsheet with a lot of financial information going down a column (about 500 rows down). Most of the cells are just typed in, but certain rows have sum formulas in them. Is it possible to copy values from another workbook, paste the values in, but somehow keep the formulas that are running through?
View 1 Replies
View Related
Aug 22, 2009
I am trying to find the total rows in multiple worksheets and to copy the row count to a summary sheet.
For Each ws In Workbooks("HR.xls").Worksheets
With ws
If ws.Name "Summary" Then
If ws.Name "Pivot" Then
View 9 Replies
View Related
Feb 18, 2014
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
View 5 Replies
View Related
Jul 30, 2009
I need the macro for deleting duplicate rows only it should keep one lowest value row.
for e.g.
Column A Column B
TS1234 100
TS1234 50
TS1234 200
Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.
View 12 Replies
View Related
Sep 17, 2013
I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.
But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?
For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....
Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .
View 4 Replies
View Related
