I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,
View 6 Replies
i have to make it to where when i put a number in it also puts in the description and
unit price just by the number i put in to a cell. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
I have a raw data worksheet with 54000 lines, in the format of six columns
In Column A is the USER Name
What I would like with if possible is code that will move the six columns of data from each individual USER into separate sheets, using Column A as the basis of the new sheets, ie the last row for each user is when the user name changes to the next one.
Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc
“The Range on each worksheet is the same ("A11:CR200") ....
I don't know how else to ask this so I will just tell you what I'm trying to do.
-I have a list of name on one column say "A"
and this list of names goes through "A1" through "A145"
-I am taking those names and pasting them via "right click" "paste special"
"Values" onto another spreadsheet.
-BUT I have to take each name one by one and paste them evenly
in every 8th row for example ( "A1" A8" "A16" "A24" ...)
-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...
Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?
For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively.
Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'.
Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column.
How can I do this?
im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
I have raw data converted from pdf into excel Workbook spanning into many sheets.Each sheet having 5 sets of records. It is highly disoriented.Some times it is in 4 columns and some times in three columns having 30 unique Records which I Want to put in rows neatly in a single sheet .Raw Data is like in table below.In some Columns There are only 20 Records.I want them in rows organized under headers.
Name: Aa bb
Fax:*5555
Team:United
Actor:Arnold
[Code]....
Problem is that in some columns there are only 10 fields present,eg: name field is there Add,state,Country is not.Also in some columns many fields are in a single cell,eg: Tel,fax,Email in a single cell.
Each Record That Needed to extract starts after : Also I can Replace all required fields like name ,add, city, to 1,2,3 if that is going to work.
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
View 3 Replies View RelatedIn the attached document, there are some cells which have been merged. For instance the cells A4 thru A7 were merged as one field. if you examine B4 which is actually made up of B4 thru B7 the data contained therein is 123. Is there a way to unmerge these merged cells and have the value (in this case 123) appear in each line of the unmerged cells? If you right click and click unmerge, it unmerges okay but does not populate each line with the data which was on the original field.
View 3 Replies View RelatedI need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
View 8 Replies View RelatedI have data in columns E through J under the headings of Monday-Saturday (E is Monday, F is Tuesday...J is Saturday). Then out to the left of the data, in column A, I have the week ending date (using Sunday as the last day of the week) which corresponds to the data under each day. There are multiple rows with different week endings but all the data is under columns E-J with the week endings in column A. So for instance, on row 13 the week ending in column A is 12/22/2013 (a Sunday) which means the corresponding data in the same row under columns E-J (the Mon-Sat columns) belong to 12/16/2013 (the Monday in the week ending on 12/22/2013), 12/17/2013 (the Tuesday in that week), 12/18/2013...and so on and so forth until 12/21/2013 which is Saturday in the week ending of 12/22/2013. Lastly, out to the right starting in column N and going horizontally out to column FD (will go out further as more days are added) I have the specific dates by day, it starts with 12/16/2013 and goes on incriminating by 1 day at a time until the end of all the dates included in the data (which at this point so happens to be 5/11/2014).
What I need done now is to take all the data which currently sits in columns E-J (the Monday-Saturday columns) and copy and paste it out under the correct specific date column that it belongs to starting in column N (going all the way out to FD) based on the week ending in column A. So with row 13 since the week ending in column A is 12/22/2013 the data in columns E-J needs to get pasted to row 13 columns N-S, then the same thing for row 14, except the week ending in column A row 14 is 1/5/2014 so the data in columns E-J row 14 needs to get pasted to columns AB-AG (AB is 12/30/2013...and AG is 1/4/2014). Below is the loop I came up with that accomplishes this, but for the reasons already stated.
Code:
Sub newtest()
Sheet2.Unprotect
Dim drd As Long
Dim rrd As Long
drd = 13
rrd = 14
Do Until IsEmpty(Cells(drd, 1).Value)
[Code] ..........
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 Replies View RelatedI havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies View Relatedif it was possible to have Excel sort and then Auto generate a work sheet.
Once a week at work I get a report that is split up via our 3 digit office codes.
Once a week I manually sort the info and split the original sheet into separate sheets based on the office code and mail it to them.
Its is simple and repetitive but takes me half a day to do due to the size.
I have found many macros for sorting multiple sheets by a single column in Excel, however, need macro for sorting multiple sheets by column "A", then column "B".
View 1 Replies View RelatedTrying to sort on multiple sheets in the workbook. The range that is to be sorted will vary on each sheet, so i would like to write the range as .end(xlDown).Row and then do the sort. So far, what I have written is giving me a type mismatch error
Code:
.Sort.SortFields.Add Key:=Range("B2:B" And Range("B2")).End(xlDown).Row, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
I'm trying to write a code to sort a variable-sized data range on several sheets. A sql query populates several sheets with data in N rows, where N varies based on query parameters).
I get a run-time error on the .sort command (method range of object global failed). i think this has to do with sorting by the same column on multiple sheets and haven't been able to debug it myself.
Sub sort()
Dim lastrow As Integer
Dim rows As Integer
Dim sheetarray As Variant
Dim colarray As Variant
Dim i As Integer
Sub DynaSort()
Dim wsSheet As Worksheet
iRow = ActiveSheet.Columns("A").End(xlDown).Row
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet2", "Sheet3", "Sheet4"
wsSheet.sort.SortFields.Clear
Range("A3:I" & iRow).Select
wsSheet.sort.SortFields.Add Key:=Range("F2:F" & iRow) _
, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
wsSheet.sort.SortFields.Add Key:=Range _
("H2:H" & iRow), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _ ...................
The problem that I has is that I cannot put focus on a cell after the sort. Xl keeps the columns selected and then when I'm trying to put in the next data Excel selects all the rows in Sheet1 also. I know how to get rid of it and continue, the users on the other hand are not that experienced with excel. fun thing, even thou the, Range.value is inside the IF it putt "pucko" in sheet1. I have a code that copies the data and then put some several functions in each sheet, after that I call the sort routine.
Code to sort sheets but that are in between two other sheets.
For example, I have the worksheets:
JE CHECKOFF | Process Checklist | AAP1 | AAP2 | CON1 | ... | PAP1 | USG1 | Posted | Instructions
And I want to sort all the sheets in between "Process Checklist" and "Posted".
I have found this code, but I'm not sure how to get it to only sort in between sheets:
[Code] .....
I have the following code (provided here some years ago) which works fine. But I now need to modify it to include an optional password and to allow sort of unprotected cells on manually chosen sheets.
[Code] ....
Sorting.xlsx
I am trying to figure out how to make a macro that can sort some numbers from Largest to Smallest within a specific range on multiple sheets. The range is only within column D starting with cell D11: (until the data ends) on all the sheets in my workbook (the number of sheets may change with time) except for Sheets: "A", "B" and "C".
In other words I want the sort to work on all sheets except the first 3 sheets which are named Sheet A, Sheet B, and Sheet C.
I have attached a spreadsheet for an example of what I am saying.
I tried this macro, written by Leith, to organize a workbook of over 100 worksheets, all named as a 3 digit number. (001, 002, 007, 004, 018, 12, etc.)
For some reason, there was no order to the sorting. Can the macro above be modified to sort my sheets in numeric order?
I have a file that i import into excel as fixed width. this is done with the code i have already written. Now im at the point where i need to "pretty" up the report.
In colum A there are Account numbers.
My goal is to have every row with the same act copied to a new sheet, and have the sheet named after the common value in colum A.
once its done there should be roughly 10 to 15 separate sheets.
I'm creating an excel sheet that is linked to several others. Basically my salesmen will enter their new prospects into their own sales sheets and that will transfer via a link to my main one. The problem I'm having is mainly with my sort.
When the information is transferred, I need the sheet to auto sort the Company names alphabetically and move any blank cells to the bottom.
Is there a way to sort out the sheets into alphabetical order in excel 2010?
View 5 Replies View RelatedI'm using links to pull our sales prospects from our salesmen's individual sheets into one master sheet for reference. Once the prospect information arrives in the sheet, I need it to sort alphabetically by customer name so that we can see which customers have been visited and don't visit the same customer twice.
So far in VB, I've been working on this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:L" & Cells(Rows.Count, "L").End(x1Down).Row).Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
But it's not sorting the way that I need to. All I really need is for the sheet to sort automatically by Company Name and for all of the 0's to go to the bottom. They are 'blank' data that will eventually be imported from the salesmen's individual sheets as they encounter new customers.
