Import & Sort

Feb 9, 2009

I need some help importing some data from one spreadsheet to another. I’m trying to basically have the data in “Harvest” imported into the “Import Here” worksheet. I’m really interested in using the data in columns A, K and M.

What this is, is a staffing program spits out the information in the “Harvest” worksheet (as a payroll report, which it does badly) but only what appears as shaded in grey or white. Anything in that worksheet shaded in green is carried out manually in order to assist importing the same data manually into the “Import Here” worksheet.

What I’d like to do is modify the “Import Here” sheet so it’ll take and process all the data from “Harvest”. Instead of doing the same thing twice I know there has to be a way to do it once but in a familiar format without it being 50 column’s wide.. although that wouldn’t matter if the print area was set correctly in "Import Here".

View 12 Replies


ADVERTISEMENT

Import Multiple Text Files & Copy Each Import

Jan 29, 2008

I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.

However, I have a problem in that my import macro gives me 'Run-time error '1004:

Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!

The code below shows is for the import macro only:

Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

View 8 Replies View Related

Import CSV Every X Minutes Without Import Dialog Appearing

Apr 1, 2008

I have a simple macro importing a .csv data file to a worksheet every 30 minutes.

When the macro runs an Import Text File dialog box displays.

What do I need to do to have the macro run to completion without the dialog box interruption?

Sub RefreshHourlyData()

htime = Now + TimeValue("00:30:00")

Application .OnTime htime, "RefreshHourlyData"

Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select

Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A1:A1").Select

End Sub

View 3 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

View 5 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Sort Command - Custom Order Or Sort Ascending Or Descending

Oct 8, 2006

Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.

Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E

If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.

Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B

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

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

Macro To Sort Colums And Protect After Sort

Nov 5, 2009

I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.

If possible i would also like a macro to protect this range after the sort,

View 3 Replies View Related

Sort - Defined Sort Criteria

Sep 20, 2006

I have an Overview sheet within my workbook that contains info from all other sheets.

What I want to do is when you click on the Sort button in the General sheet it will activate the Overview Sheet and

- first off sort all sheets starting with AJ together, then all sheets starting with CJ together and then all sheets starting with PJ

- then the next thing i want it to do is sort all Ajs by their start dates, sort all CJs by their start dates and the same for all PJs.

Now up until now I was keeping all sheets that began with AJs together in the workbook and so on so I was able to use the following sort function

Sub Overview_sorting() ...

View 5 Replies View Related

Sort Horizontally. Sort Across Columns

Dec 30, 2006

I am trying to sort a long range of text that is placed horizontally in a spreadsheet. I can do it vertically with the sort function in Excel but it does not seem to work for text that is placed horizontally. Example is as below:

Inventory Accounts Human Resources

View 2 Replies View Related

Sort And Group Names Then Sum Associated Numerical Values For Each Group - Sort Total

Apr 18, 2014

I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:

Last First Amount

Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200

What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:

Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100

Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.

View 1 Replies View Related

Sort Tabs By Color And Sort Color Groups Alphabetically

Oct 14, 2012

I've located the following color sort VBA that sorts worksheets by color and it is working well. I'm trying to modify the code to sort each of the "color grouped" sheets alphabetically. That is, sort by color and then each color group sorted alphabetically.

VB:
Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)

Dim i As Long
Dim j As Long
Dim ShtC() As Long
Dim ShtN() As String
Dim t, n As Long
Dim lngSU As Long

[Code] .....

View 5 Replies View Related

VBA Code To Sort Data - Run-time Error When No Data To Sort

Mar 4, 2010

I 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

View 9 Replies View Related

(Match & Sort) Sort Columns B And C So That A And B Match Up Numerically

Feb 26, 2009

I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.

View 3 Replies View Related

Import From Web

Feb 27, 2008

I have the tedious task of building a visitor database, i have a web page with over 300 records on it, each record containing information such as company name, address, phone number email and this is only the first web page i have to visit, that's already over 1000 copy and pastes.

I'm wondering if there is a way to just capture all of the information on the web page and then populate my excel spreadsheet with information falling under the appropriate headings.

I tried the method of right clicking on the web page and said import to excel, but as you can imagine this just resulted in all of the data appearing in one column and i would still have to move all the information around so it is not of much help.

View 9 Replies View Related

Web Import

Apr 30, 2008

I am trying to import some data of the web and have managed to make it work but it is very slow - 90secs per loop - am I doing something wrong or is it just that slow?

Sub GetLonglat()

Dim InCellLoc As String
Dim InCellCol As String
Dim InCellRow As String
Dim NameValue As String
Dim AddressValue As String
Dim OutCellLoc As String
Dim OutCellLoc1 As String
Dim OutCellLoc2 As String
Dim OutCellCol As String
Dim OutCellCol1 As String
Dim OutCellCol2 As String
Dim OutCellRow As String

View 9 Replies View Related

VBA Import From Web

Dec 2, 2009

Is there any way to change parts of a URL within a macro using the import from web function?

The import from web function as a macro is the following:

With ActiveSheet.QueryTables.Add(Connection:= _

I would like say the "4457" part to be dynamic. It can either change by user input, from another function..etc doesn't really matter. The problem is can this change? by string concatenation perhaps? or something else?

View 9 Replies View Related

Import Data From Different Tab

Jun 17, 2014

I have a tab in my workbook with 10 various items 50 pieces each, in the 2nd tab i have about 100 orders for these items. What is the best way for me to find out for each of the 10 items how much i sold?

View 6 Replies View Related

Excel Keeps Using 25% CPU After XML Import

Apr 5, 2014

So I finally got a workaround for importing data from my Web Service to Excel.

It is currently working, but after I have imported the data I see from the Task Manager that Excel keeps using 25% CPU. And it also sometimes lags and mini freezes in Excel.

If I delete the row that was juts imported it immediately goes down to 0% CPU use and the lagg/freezes are gone.

View 1 Replies View Related

How To Import TXT File

Jun 22, 2014

import .txt file to excel.

I have below information in txt file

Microsoft (R) Windows Script Host Version 5.8Copyright (C) Microsoft Corporation. All rights reserved.

------------------------------------------
System Details
------------------------------------------
Host Name: XYZ
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================

***** script completed - exit code: 0 *****

------------------------------------------
System Details
------------------------------------------
Host Name: XYZ1
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================
Disk 2
=================
Disk 2DeviceID: D:
Disk 2Disk Size: 500GB
=================

***** script completed - exit code: 0 *****

required excel format.

Host Name:| CPU COUNT: |Total RAM: |Disk 1 DeviceID:| Disk 1 Disk Size: | Disk 2 DeviceID: |Disk 2 Disk Size:
XYZ| 2 | 4 GB| C | 112GB | |
XYZ1| 2 | 4 GB|C | 112GB | D | 500GB

View 2 Replies View Related

VBA To Import Sheets With Same Name?

Jul 10, 2014

I want VBA Code to import multiple sheets bearing same name one below the other which are in different workbooks.

Drive "D" contains "ALL DATA" folder which contains multiple workbook and all workbook contains sheet named "fair"

View 3 Replies View Related

Import Text Using ADO

Aug 22, 2008

The task given was to import selected info from complicated text file and record into excel.

Here is the simple way of showing the txt format (consist of 2 records for example):
=================================================
Proxy Id 123
MO ABC
=================================================
GID_Group TAX8200
Account 0 (INTRA)
loadSharingCandidate 0 (FALSE)
RelationType 0 (TRUE)
Offset 0
Priority 2



=================================================
Proxy Id 123
MO ABC
=================================================
GID TAY8200
Account 0 (INTRA)
loadSharingCandidate 0 (FALSE)
RelationType 0 (TRUE)
Offset 0
Priority 2

How can i have the output as per attachement? I am not good in importing text files. Shouldi use something call ADO? or what is the most easiest way for me to start?

View 11 Replies View Related

Import Worksheet Add-in?

Oct 7, 2008

I found this script on the net which I think is very good for importing multiple workbooks/worksheets into a current workbook.

It works fine when running as a module, but when I change it become an add-in it gets a bit a confused when trying to move sheets.

I'm pretty sure its something to do with the way thisworkbook is referenced. From some of the suggestions I see it could fixed by setting-up a class module, but it sounds like i'd have to do that for every workbook i wish to import worksheets into.

View 2 Replies View Related

Sql Import To Access

Dec 20, 2008

The size of the table I'm importing will change, so i would like for the code to not matter on size. Also its, gonna be large too. The sheet will always be the same and the column headers will match for excel and access.

View 2 Replies View Related

Import Macro ..

Jul 1, 2009

I have only ever created macros in existing workbooks. Is there an easy way to copy a macro from one workbook to another, without having to run a new macro in the new workbook and then paste the code into it from the old workbook?

View 7 Replies View Related

Import, 2 X Pop Up Box & Consolidate

Jan 13, 2010

Then it will ask the user what name it wants to find.... then user inputs the name and the macro searches the 2 newly imported worksheets in column A and B, and if it 'contains' the name then will copy it to a results worksheet (and copy row 2 as a static line from each of the work book to differentiate which sheet it came from) ....

View 14 Replies View Related

Import Data

Jan 14, 2010

I have this VBA macro which imports data from a text file. I do not know exactly how to make it good.

I have two sheets, named <INDATA> <Drawing_list>

On sheet <Drawing_List> I made a command button with this ....

View 14 Replies View Related

Import CSV Macro?

Oct 31, 2011

I have managed to come up with the following macro. The problem I have is that some of the data are imported in text format, while other data are imported in general format.

I have a large data set, with more then 5,000 rows, and 2,000 columns, and I would like ALL the data imported at text. How can I modify this macro to make that happen?

Code:

Sub ImportData()
With ActiveSheet.QueryTables.Add(Connection:= _"TEXT;C:Users*****ATTRIBUTES-ModifiedExport.csv", Destination:=Range("$A$1"))
.Name = "ATTRIBUTES-ModifiedExport.csv"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False

[Code]...

View 1 Replies View Related

How To Import Some Text From PDF

Apr 11, 2014

I was able to import some text from a PDF to excel. My dilemma is that the text is now one long row with CODE in column A then its description in Column B...the next entry has a Code in column C with its description in column D...etc...down the line.

I need Column 1 to be all the codes with description in column B thus allowing Vlookups.

Here is sample:

A B C D E F G H I J
AC
Araucanian
AD
Adangme
AE
Afroasiatic
AF
Afrikaans
AH
Amharic

Any text column type deal?

View 3 Replies View Related







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