Free Microsoft Excel 2013 Quick Reference

Sorting the tabs Results

Hi,

I am currently exporting data to a CSV file so to be viewed by Excel.
I need to support the exporting of data in Japanese so therefore the
CSV needs to support Unicode. The issue is that Excel seems to have
problems differentiating a file that contains Unicode chars. After
exploring the internet I have found that you need to use a BOM in the
first few bytes of the file to tell excel what sort of encoding has
been used. I have found different codes to use but cannot seem to
find much information on what the codes actually stand for. Here are
the codes I have tried.

0xEF 0xBB 0xBF This actually maintains the comma separation but the
text is garbage I am assuming that this is because this one sets UTF-8
encoding when I am just using straight unicode.

0xFF 0xFE This actually makes Excel open the CSV file in Unicode
correctly displaying the Japanese characters but it defaults to a TAB
deliminated separator instead. At the moment I can simply use tabs to
separate fields instead of the commas but I anticipate maybe not all
spread sheeting programs have the tab deliminated feature?

I guess what I am after is some more information on BOM's and how
Excel interprets them. Does anyone know of a BOM code that will tell
Excel that the CSV file is comma separated and that it contains
straight Unicode.

Thanks for taking the time to read this and have a nice day....
Danny Mosquito

Hi All,
I am receiving a quite large number of tab separated text files with
information each week (50+), that I need to import into an Excel sheet and
then work with=produce reports
It is quite time consuming to run the File import utility for each file, and
I would therefore like to create a macro assigned to a button that should do
the following.

For each text file in a folder, sorted by file name or creation date, do the
following:
1. Import the tab separated data, and append it to the already existing data
in the active sheet. The information should be imported like the example below
2. Move the file to another folder, an "archive"

If I have this I can easily create a rule in Outlook that stores my text
files in a certain folder, open excel, push the button, and be ready to work.
Would be great!

The file that should be imported looks like this:
Header1
Header2
Colummn Header 1 Column Header 2 Column Header 3
ROW1-1 ROW1-2 ROW1-3
ROW2-1 ROW2-2 ROW2-3
ROW3-1 ROW3-2 ROW3-3
Footer1
Footer2
Footer3

Ideally, I would like the excel spreadsheet to look like this:
Header2 ROW1-1 ROW1-2 ROW1-3
Header2 ROW2-1 ROW2-2 ROW2-3
Header2 ROW3-1 ROW3-2 ROW3-3

I have tried to write macros for this but cannot make it work. If all cannot
be done (I realize this might be quite complex) I would appreciate any help
with getting as close as possible to the ultimate solution!

Many thanks,
David

Hi

Could someone please please help me with the following;
I am needing to do a spreadsheet with 4 different tabs that when you click on a merge button it will automatically transfer all the data to another worksheet labelled Completed. This would include if one of the tabs deletes a line the line will automatically delete off the completed sheet when the merge button is clicked.
ON all sheets they have to be sorted via date descending date order
They all have to have a button which will collate the information.
I need to lock certain columns.
I need to create a print macro button which when clicked will allow the user to print automatically.

Is this possible? I would appreciate any help, even if half of it

If my workbook has sheet names titled from a-z, but they are out of order is it possible to order the workbook? If so, how? I don't want to click and drag each sheet individually.

is there any way to sort worksheets other than using the move or copy option?
How to alphabetize multiple sheets at one time.

Hi All

I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.

The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.

Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner?

Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.

Can anyone amend my code so that it takes the list order into account?

Thanks in advance.

Sub Custom_Sort()
    Range("A1:M190").Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range _
        ("H2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
        
    Range("A1:M190").Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range _
        ("C2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=6, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
        
End Sub


I have a macro in which I am trying to sort data. The data is stored in a table and every column has a unique header. One of the columns have a header called “Sec type”. If the vale in that column is “GOV BOND” then I want to look at the column marked by the header “Sec ID”. The data in that column is stored like this: NAME YIELD MATURITY. For example: ACGB 4.75 0311, T 5.10 0420, FROG 3.11 1020X. Now as you can see the “Sec ID” has the same form but it differs in terms of characters etc. I want to sort the entire table based on maturity. Thus, I want to single out the maturity and then sort the list. For certain reasons I do not want to add new columns etc. but handle this “inside” the program.

The way I attack the problem now is that I use text-to-columns and tries to store the new columns in the matrix. I have spare room for the new data so that is not a problem. My problem is that I do not know how to refer to the places in the matrix where I want to store the data. Let me show you some code:

I loop through the table by looking in the “Sec ID”-column. For every row I look to see if the text in “Sec type” is “GOV BOND”. If so I increase the matrix and then I use text-to-columns (from a recorded macro).

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
r = r + 1
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
ReDim varDataMatris(1 To tableLength, 1 To r)
rngSecID.Offset(r, 0).Select
Selection.TextToColumns Destination:=varDataMatris(tableLength - 2, tableLength - 1, tableLength), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
End If
Loop

My problem is that I do not know how to refer to the proper destination. Now I have: Destination:=varDataMatris(tableLength - 2, tableLength - 1, tableLength) which is completely wrong. I want to place the data retrieved from the text-to-columns in the rightmost columns in the matrix. The row is to be the same as in the list i.e. r. I really have no idea how to write that. Please help me out! Any help is very much appreciated but I cannot change the setup very much but I need to find the code for how to specify the right address in matrix! Many thanks in advance!

I created this macro that when executed will use a second sheet I created to
sort te contents in the cell I'm currently selecting. I would like to take
the process one step further and have this code loop until there there isn't
any more data to sort. Here are the specifics of the sheet. All of the data I
need to sort is in column E starting on row 2. The number of rows in the
sheet is not constant. It coulld be 50 rows or 3000 rows.

My next step will be to move the functions I have on the other workbook to
the same workbook as the data but on a different sheet.

Sub Sort()
Application.DisplayAlerts = False
'
' Sort Macro
' Macro recorded 12/27/2005
'

'
Windows("Code Report Tool.xls").Activate
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
Rows("3:3").Select
Selection.ClearContents
Windows("Bank XXX Code reports 12-23-05.xls").Activate
Selection.Copy
Windows("CODE Report Tool.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1)), _
TrailingMinusNumbers:=True
Rows("3:3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Range("A19").Select
Selection.Copy
Range("A8").Select
Windows("Bank XXX CODE reports 12-23-05.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.DisplayAlerts = True
End Sub

I am currently working within a workbook with a 1000 columns, I want to sort each individual column from smallest to largest but I am unable to do so. Selecting all the columns and clicking the smallest to largest sort tab does not appear to sort each column individually.

How can I go about resolving this so I don't have to manually sort 1000 columns?

Working within sheet3 of my current workbook (Excel 2007) if you can suggest a macro/script

Thanks

Hi there,

I have for example a spreadsheet with two tabs "A" and "B".

In tab "A" i have a formula like this =A!A1*A!B1+B!$A$1 but if i do a sort ascending on column "A" the formula will not reference correctly e.g i end up with something like this =A!A11*A!B11+B!$A$1 but if i remove the same sheet reference then it will reference correctly when i perform a sort.

I have attached an example and any explanation why same sheet refernces cause this will be well received.

Thanks
Mark

I am trying to make sure a colomn is sorted after a worksheet tab is clicked.

Worksheet Period1 has names in column K, this column has to be sortable by the user.(This sorts column K by Alpha and then sorts the entire worksheet accordingly.)
Worksheet Individual has combo boxes that receive these names.

I need column K on Period1 sorted Alpha (A - Z) everytime Individual is accessed, this way the names match up to the combo box reference numbers. I am calling macro's using the reference numbers.

Sub LoadWorksheetNames()
  
  Worksheets("Individual").DropDowns(2).RemoveAllItems
  
  With Worksheets("Individual").DropDowns(1)
    .RemoveAllItems
    For Each Wks In Worksheets
      If Wks.Name <> "Individual" Then
          If Wks.Name <> "Rubric" Then
              If Wks.Name <> "Standards" Then
         .AddItem Wks.Name
      End If
    End If
  End If
      Next Wks
    .Text = "Select a worksheet..."
End With
  
End Sub

Sub LoadRangeNames()

  Wks = Worksheets("Individual").DropDowns(1).Value
  
  With Worksheets("Individual").DropDowns(2)
    .RemoveAllItems
      For Each Cell In Worksheets(Wks).Range("K4:K54")
        If Cell.Value <> "" Then .AddItem Cell.Value
      Next Cell
    .ListIndex = 1
  End With
      
End Sub

Sub RunMacroInList()
   
  Dim R As Long
  Dim Wks As Worksheet
  
    ' Get the Worksheet the Macro IDs are on
      Set Wks = Worksheets(Worksheets("Individual").DropDowns(1).Value)
    
    ' Calculate the row number of Macro ID: DropDown Selection + 3
      R = Worksheets("Individual").DropDowns(2).Value + 3
   
   ' Run the macro based on the Macro ID
      Select Case Wks.Cells(R, "DX").Value
        Case 1
          Call IndStu4
I have tried doing this with Worksheet_Activate, but it goes into a loop, so I tried using Application.EnableEvents = False then Application.EnableEvents = True and I am not sure it has ever been set back to True. How can I confirm this?

Can anyone help me with this?
Ken

A Hlookup from another sheet provides the data, and displays it on a graph.
The dates used for the Hlookup are provided by a user, these dates can be
anywhere from 1 week to 6 months apart.
The Graph as it is mostly blank at the moment when set to 2 weeks as it
shows the other 5 and a half months as blank. I thought a Macro might sort
the problem for me, so on the Options tab there is a button that runs the
macro, I had hoped that I could use my limited (very limited!) knowledge of
VBA to change a recorded macro.
To that end, on the Data sheet in cell B99 is a =concatenate that currently
reads =B101:K101 the K part dependant on how much data is present.
I recorded a macro where I changed the graphs "Category (X) axis labels:" to
=B101:K101 when I opened the macro it looked alot like
Sub Resize_Chart()
ActiveSheet.ChartObjects("Chart 28").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(2).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(3).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(4).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(5).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(6).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(7).XValues = "=Data!R101C2:R101C11"
End Sub

And I have no idea how to change this to use the range in Cell B99 on the
Data sheet... any idea's?

Hi guys

Am working on a macro which needs to do the below
Data is sorted by A. Where 1 row matches the row below on Columns A, C, ABS D, H, I and J both rows should be copied to Boxes tab. Where there is only a single a/c number or both rows don't match on any 1 of the criteria- rows should remain on the Data tab.

Appreciate any help

Thanks

Noel

hello all,
I have a work book that i stored data. There is 30 tabs or sheets(i:e each
sheet is used to store a day activities). At the end of the month i would
like to summarized each tab's or sheet data on the summary sheet(the summary
sheet is the last tab on the wookbook).
I would like to have a macro that copies each entries on each sheet and
store them on the summary page then sort the information in alph. I have
tried record macro but that does not work. i will appriecate and help
thanks in advices

I have a workbook with a tab of data for each month. I need YTD totals, however the names of the employees are not on the same line in each tab. So, it's not simply a sum of Jan!B2,Feb!B2, etc. That was my first thought; to do so and sort that column by name, but people come and go, so even then the name in A20, for example, could vary month to month.

On the YTD tab, the names would be in A5:125. Need formula for B5 that will search for a match of A5 (name) in the range of all prior tabs, and then give a sum.

I know the actual worksheet is beneficial. Removed names, but the data is there.
Any help greatly appreciated!

#2 - some #VALUE errors there. formatted h:mm:ss, yet if the total time is less than an hour it seems a 0 has to be inserted to avoid the error. Surely there's a fix for that?

Hello Everyone,

I dont know whether this question has already been asked before, I have not been able to find any specific solution for this.

I am enclosing an excel spreadsheet with the following tabs:
1. Hire_Report: Which is a data dump. Every month I get data in this format. This actually runs into over 20,000 rows, however, i have cut it short
2. Pivot: I have created this to sort my data in specific desired format (from Hire_Report tab)
3. Main: This is again a formatted representation of the Pivot tab along with some calculations. I prepare this tab by copying the 'Center' column data from Pivot tab and paste those center numbers in the 'Center' column of this Main tab. Rest everything in this tab is linked with formulas (Vlookup, etc) which pulls the data from Pivot tab

I am looking for a Macro which can directly copy the 'Center' column data from Pivot tab into the 'Center' column of the 'Main' tab. In this tab then Vlookup function will automatically pullup the data from 'Pivot' tab. But it is important to note that the length of my 'Column' data on Pivot tab will keep on variying depending upon the data I get on the 'Hire_Report' tab. Thus, It will be boon to have a Macro which can automatically adjust the length of 'Column' data while coying it from the Pivot tab so that Vlookup function works well in the 'Main' tab even in cases when (1) column data reduces (2) column data is increases. i.e. the Macro should automatically adjust/fit all the formulas in 'Main' tab as per the length of the 'Column' tab

Thanks,
Kapil

Sorry reposting because of tabs format.

Hi, I am creating a sheet that will list who can buy bus passes. Here is the
lay out.

A4 B4 C4 D4 E4 F4 G4
H4
# Last name First Name NB # Last name First Name NB
1 4
2 5
3 6

I believe I need a macro to sort by Last Name. Now rows under A4 and E4
can’t change. They will hold the numbers of how many people have been
approved.

As you can see it will have two columns of names per page. It will have 88
names per page, three pages for a total of 264 names.

We are a non-profit organization. We help homeless and low-income people.
These discount bus pass are sold to qualify people and we make no money from
the sales.

Those I know how to use excel my skill level is not up for this. Any help
you can give will be appreciated.

Each month I have to break the links in about 40 workbooks, each with 10-15
worksheets in each.

Currently, I open each workbook, group all the tabs and then Copy/Paste
special values to break links.

Is there an easier way to this - sort of like a batch???

Thanks,
Tom

Hi! My original post just kind of slipped through the cracks.

I have a workbook that pulls sales from an Access database. The data is set
to update each time the workbook is opened. I created a summary worksheet
to look at the data tab and just show current month's sales by category
which is basically a formula for each row to make current sales visible and
then I have a simple Macro to sort the data. There are a few issues I have
here. First, I had to create formulas for thousands of rows to make sure I
have enough rows to cover data in the data tab, so if the sales totals
exceed the rows I have for the sales summary, then I miss sales. Second,
for some reason now, when the Access data updates, it messes up the formulas
in the summary tab and I get #ERR in the formulas. Third, my Macro just
sorts the data bring it to the top so the blank rows go to the bottom. I
have to sort in inverse order, so the summary is backwards.

A little background on formulas:
* I have named ranges for the Access data tab that are using OFFSET so all
data is included in the named range regardless of how many rows it fills.
* The formula in the summary is just a formula that looks at each row and
checks to see if it is within the current month and then shows the
description, date and sales price. If it is not in the current month, it is
just blank.
* The sort Macro just resorts the data range (about 2500 rows) descending so
the blanks go to the bottom rows.

What I would like to do is figure out a way to have the summary page just
find the monthly totals for that month in ascending order. Is there a way
to do this so I can avoid creating thousands of rows of formulas and just
have it fill in the data? I would prefer not to use autofilter.

Thanks!

Excel 2000 spreadsheet will contain a large number of sheets.
I would like to name the sheets and sort alphabetically.
If this is possible, how can it be done?