Sort Data On Multiple Worksheets

Aug 16, 2006

I am using the code below to transfer data from a single sheet to approx'
200 sheets. These sheets are staff training sheets, one per staff member.
This code works great. What I would like to know is, is there a way to then sort the data on these sheets in decending order? I have tried on sheet change but this seems to stop the transfer to other pages.


Sub Tranfser()
Dim shtTemp As Worksheet
Dim lngOutRow As Long
Dim rngData As Range
For Each rngData In Range("A5", Range("A5").End(xlDown))
Set shtTemp = GetWorksheet(rngData.Offset(0, 1).Value)
If Not shtTemp Is Nothing Then ..........................

View 9 Replies


ADVERTISEMENT

Sort Data To Multiple Worksheets

Dec 14, 2006

We are trying to sort a spreadsheet by the data in column I. This column refers to a state. I need help creating a macro that can sort column I so that different states go into different worksheets.

States ME, NH, MA, RI, CT, VT go to a worksheet titled 357899, states NY, NJ would go into worksheet 351835, states MI, IN, OH would go into worksheet 351857, and everything else would go into worksheet 351836. The main data worksheet where the info is being sorted from is named All_Accounts. Column I has a header labeled State, so data actually starts in Row 2. I need the full rows copied to the new worksheets while leaving the main All_Accounts worksheet in tact.

View 9 Replies View Related

Sort Data Ranges Across Multiple Worksheets

May 2, 2008

I have been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?

View 2 Replies View Related

Excel 2010 :: Sort Worksheets Alphabetically And Keep The Data In Worksheets

May 15, 2013

I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.

View 2 Replies View Related

Selecting Multiple Worksheets - Sort All

Mar 14, 2012

The code im using all worksheets. How do I make this sort all but the first

For Each WS In ActiveWorkbook.Worksheets

If WS.Name "Sheet1" Then

Range("A1:X2270").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

View 1 Replies View Related

Sort Set Range Across Multiple Worksheets

Oct 9, 2009

I would like to use a macro to sort multiple worksheets simultaneously. I need to sort on last name (column A) then first name (column B) and my data does not start until the 8th row (A8:AF8). The data range should be the same for all worksheets that I need to sort. I found the code below here on ozgrid (Dynamic Sort Across Multiple Sheets) but I'm not sure if its appropriate or the best way to customize it so that the 1. Can sort on two criteria

2. Is specified to nonblank cells in a specific range, A8:AF8 and below

Sub DynaSort()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
With wsSheet
.UsedRange.Sort Key1:=. Range("B14"), Order1:=xlAscending, Header:=xlYes
End With
Case Else
'Nothing
End Select
Next wsSheet
End Sub

View 9 Replies View Related

VBA To Sort Multiple Worksheets By Number Order

Feb 26, 2014

I found the code below on the Microsoft website and it works except it didn't treat the worksheet tabs as numbers so the sort is 1, 10, 100, 101 etc.

How can I get it to treat the worksheet values like numbers and sort accordingly?.

Code:

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'

[Code]....

' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub

View 1 Replies View Related

Sort Data On All Worksheets ..

Sep 22, 2007

I have a worksheet of about 75000 rows so I have to use 2 worksheets. I want to sort the data in column A in ascending order so I want to start at 4999 until whatever number happens to be in row 65536 then continue sorting the next highest number in another sheet. I tried using this but it didn't work: ..

View 2 Replies View Related

Sort Data On All Worksheets Active And Other

Aug 21, 2008

It sorts the ActiveSheet, but none of the other sheets and there's no runtime error. I am using this on a test workbook with the same data in 5 worksheets.

What's wrong with this code?

Sub SortSheets()

Dim ws As Worksheet

For Each ws In Worksheets

Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Next ws

End Sub

This looping structure works for PageSetUp, but not this Sort.

View 5 Replies View Related

Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String

[code]....

Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

View 4 Replies View Related

Sort Worksheets Using Custom Sort To Choose Certain Word?

May 30, 2012

As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.

is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.

View 1 Replies View Related

Consolidate All Data In Multiple Worksheets Of Multiple Workbooks In One Master File?

Jul 12, 2014

I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.

All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.

I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...

View 4 Replies View Related

Sort Data Into Multiple Rows

Dec 2, 2008

I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this

Value1X1,X2,X5
I need it to change to this
Value1X1
Value1X2
Value1X5

View 5 Replies View Related

Sort Data Over Multiple Columns

Nov 6, 2009

I'm trying to figure out a way ( excel 2000) how to sort data over a range of columns.

Attached is a sheet.

So what I am looking for is this:

Bottom 10 for target 1, target 2, target 3.

I can sort them indervidually, but is there a way to sort the all?

Or would I need a agent column for each target to sort?

Or maybe there is a way to sort the data so it would work out that if they are in the bottom 10 of lets say 2 of the targets but not all 3 they would still show in the bottom 10?

View 7 Replies View Related

Macro To Sort Multiple Rows Of Data

Sep 16, 2008

I have a report that I drop down into Excel that is a transaction report for securities. The data for each security is in two rows. I would like to write a macro that would sort each two-line group by a certain cell in the group. If I could make the macro request the number of rows and columns in each range and the cell address of the cell to sort by, I would be able to use this macro for many different reports. Also, there has to be some way of telling the macro where to begin and where to end. I have some experience with macros although generally I "Frankenstein".

View 9 Replies View Related

Non Consecutive Rows Sort Data With Multiple Ranges

Feb 23, 2010

Once again, I'm seeking some further assistance to an issue that I have yet to resolve.

I have several rows of data which I'd like to be able to sort by the info in column "A".

The data rows are not all consecutive. The range for which the data resides are:

Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.

View 8 Replies View Related

Macro To Sort And Align Employee Data In Multiple Cols To A Header Row

Jul 9, 2014

I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.

My Source excel file looks like:

ABC A-101 B - 202
DEF B-203 C - 100

The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.

The output I want is:

A B C D
ABC 101 202
DEF 203 100

There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.

In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc

View 1 Replies View Related

Macro To Automatically Sort Multiple Column Groups Separately As Data Is Entered

May 20, 2014

I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.

Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then

[Code]....

View 1 Replies View Related

Consolidate Data From Multiple Worksheets

Jan 7, 2009

I have a workbook with 7 different worksheets (site_worksheets) containing data about various sites. I need to copy all this data into a single worksheet (worksheet_a). I want worksheet_a to update itself when another row of data is added to any of the site_worksheets.

View 12 Replies View Related

Summary Of Data From Multiple Worksheets

Jan 11, 2010

I have 9 sheets of sales data, some with over 50,000 records. I need a way to present this information in another worksheet so I can bring up just the data based on individual brands. I understand a pivot table is needed however I cannot see how to gather data from multiple worksheets.

For example:

I need to look at all the different sales for Product A, which appears in multiple worksheets. Product A is broken down to store-level, with records for number of sales in each store, one record for units and one for value (for the same store and product). Is there a way I can get the data for all of Product A in one worksheet so I can then easily add the totals across all stores by both units and value?

E.g.
Store 1 - Product A - 10 units
Store 1 - Product A - £20
Store 2 - Product A - 7 units
Store 2 - Product A - £14

View 2 Replies View Related

Reconciliation Data Of Multiple Worksheets

Oct 31, 2009

The macro able to extract the value of each 'Summary' sheet A1 and B1
But it couldn't identify the value of 'total day(s)' of work/leave/unpaid leave.

This is because the location (rownumber) of 'total day(s)' rows is not same for each worksheet.
(depending the number of staff)

Actual Result:
department:laundry
department:marketing
department:security

Expected Result:
department:laundry5361
department:marketing60146
department:security 2875

View 2 Replies View Related

Consolidating Data From Multiple Worksheets

Apr 11, 2008

I'm trying to consolidate inventory for my department. I have Part#s in (column E) of all the worksheets and the amount of the product in (column C). I need a formula that finds the specific part# (in column E) and adds up the total amounts (in column C) in another worksheet has the part#s and amounts in same column.

View 14 Replies View Related

Importing Data From Multiple Worksheets

Feb 23, 2012

Im trying to import data from multiple worksheets. The data from these worksheets are scattered throughout the different work sheets.

I would like to extract the specific data from each worksheet into a master template that ive created. 80% of the worksheets templates match my master template.

Is there any way to do this without taking 1 week to complete?

View 1 Replies View Related

Consolidation Of Data From Multiple Worksheets

Dec 20, 2013

Consolidation of data from multiple worksheets of a workbook in a different workbook.

The, to be copied ranges are static at column B, E & AB. However, the data is required to be copied below each other for each sheet. Also the name of sheet is required to be assigned to every copied data in consolidated file...

Suppose i have 5 sheets with data... then in the consolidated sheet i need name of "sheet1" to be assigned to all the copied records and so on for all the sheets...

Sub trial()
Application.ScreenUpdating = False
Dim Wb As Workbook
Dim path As String

[Code] ........

View 1 Replies View Related

Copy Data To Multiple Worksheets

Jan 9, 2014

I run a report each month and I have several hundred lines of data with Heading 1 being the customer Name. I would love to copy this information to individual sheets based on the customer name as showed below (Output).

Raw Data:

Sheet 1
Heading 1
Heading 2
Heading 3
Heading 4
Heading 5

A

DATA
DATA
DATA
DATA

[Code] .....

Output:

Sheet 2 = Rename to A
Heading 1
Heading 2
Heading 3
Heading 4
Heading 5

A

DATA
DATA
DATA
DATA

[Code] ...........

View 1 Replies View Related

Combine Data From Multiple Worksheets

May 23, 2014

I have data in multiple worksheets and I want it all combined in 1 excel sheet. The 1st worksheet is named as A and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. 2nd worksheet is named as B and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. and there are some more worksheets like that. The start point of data will always be same but can end till any row. Can I get a code for collating all together in one sheet,

View 9 Replies View Related

Data Validation All At Once Across Multiple Worksheets

Jan 2, 2007

I have 37 workbooks that are timesheets for employees for calendar 2007. Each one has 52 tabs for the weeks of the year. Each worksheet has a particular column that needs to have a drop-down selection. That's working fine as long as I do it one worksheet at a time.

Since I don't relish doing this 1,924 times (37 employees x 52 weeks), I opened a workbook and selected all tabs (first, shift, last), but when I tried to highlight the cells that need the drop-down info, "Validation" under "Data" was grayed out. I checked another sheet and found the same thing. As long as I do it one worksheet at a time, it works fine.

Can data validation, for the purpose of adding drop-downs, not be done across multiple worksheets within the same workbook?

View 9 Replies View Related

Combining Data From Multiple Worksheets

Jul 26, 2007

would anyone be able to write the code that will combine the data from all rows from all worksheets within a workbook. i've struggled with this one....

here is the deal:

all worksheets have the same columns and column headings but differnet amount of row counts. the width of the sheets is to Column "M" or "13" and there are no blank columns.

There is no need to have the columns headings repeat within the compiled worksheet.

the amount of worksheets will vary depending on when i run it so it will not be able to use specific naming conventions.

View 9 Replies View Related

Clear Data In Multiple Worksheets

Aug 29, 2008

is there a way to clear data in all worksheets according to the ranges specified in the code below, I have around 34 worksheets in which i need to clear data and i dont want to write the below code 34 times.


Sub Clear()

With Sheets("Rec")
.Range("A7:C7").Clearcontents
.Range("E7:O7").Clearcontents

End With

End Sub

View 9 Replies View Related

Move Data Multiple Different Worksheets

Feb 3, 2009

I have a summary sheet where I am trying to move data from 20 to 30 different worksheets (in the same workbook). I am refrencing a code from column a in the summary sheet. Then I want to lookup that code in a column in a different worksheet then go down 150 rows and over 1 column to return the value from that cell.

abStandardsUnit Time (Minutes)CodeTaskPrepFabClean UpTables & BenchesFP10106' Table Process StepsA6SHAssemble 6' Table Seat Holders0.151.750.19A6StAssemble 6' Table Seats0.603.150.13A6TAssemble 6' Tops5.4715.002.22

So I am trying to use vlookup to find the code "a6sh" in a worksheet, then once I find that code (column), I need to go down 150 rows and return that cell value into the "prep" cell. The value in the "prep" cell is an average of the 150 rows, one column over from "a6sh". I don't want to have to do this manually.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved