I m importing data and I have not seen anything remotely similar in any previous posts. I need to import data using an array for the different filter parameters. The size of the array will vary. The underlying problem is that VBE appears to limit the number of characters that are allowed across one row. If the array size is only one the code works fine - any larger and it crashes.
Sub ImportDataWeek()
Dim state As String
Dim Test As String
Dim NewStatement As String
Dim restate As String
Dim i As Integer
state = ""
For i = 1 To (UBound(TestType) - 1)
Test = "(qryDataRawValues.Test= '" & TestType(i) & "') AND (qryDataRawValues.Prod_ID= " & ProdID & ") OR "
state = state + Test
Next i
NewStatement = Mid(state, 1, Len(state) - 3)
restate = "WHERE " & NewStatement
Sheets("RawData").Select.....................
building dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following: ________________________________________________ Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev) COGS ||||||||||| + (% of Total COGS) Margin |||| + (% of Total Margin) -------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: | MN | Midwest | Major | Revenues | $$$ MN | Midwest | Major | COGS | $$$ MN | Midwest | Major | Margin | $$$ MN | Midwest | Major | % of Total Revenues | %%% MN | Midwest | Major | % of Total COGS | %%% MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
I'm trying to get a user friendly button to simply select then import a csv file into excel. I recorded a macro which runs on clicking a command button. below is the
I have a question, I have a pivottable with data (only numbers as values). In this pivot there is a report filter which filters current month and last month. Is it possible for the pivot to show the data as a difference between current month and this month and if so where to start with the vba code to let it substract from each other?
I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.
I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.
There was no problem until I made a couple of changes last week:
1. Adding a simple pivot table
2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.
I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.
I have a column with 3 different types of data in 30000 cells in Column A (Example model attached). This contains data types (i) Alpha Numeric Data (ii) Numbers only (iii) email ids
Unique identifiers for the above 3 are: (i) Alpha numberic data : ABC (ii) Numbers only : 1375 (iii) email ids : @
I currently have almost 20,000 rows of data and I am filting down to a particular value in column D. However, as the filter runs you can see that after it finds the fields it keeps running like it should to the end to make sure there are no additional values. Is there some faster way to search this many lines? Would it be faster to sort the worksheet first and then do something with a sorted worksheet or what are my other options?
I am working on a complicated macro. The macro is suppose to move certain data set to a specific worksheet if an if statement is "True."
As of now the macro, inserts the row into a new sheet but it is inserted into Row 1. My data set is in Sheet A is up to Column U and I want it to be inserted in Column B on Sheet B. The code below inserts the entire row into Sheet B starting with column a.
[Code] ........
This variation of the code moves the entire data set from Sheet A to Sheet B and inserts it in Column B as wanted, but it does NOT follow the Criteria of "true", it brings the entire data set even those with "False".
PIVOT TABLES -- Old data remains in pivot table filters
One more Pivot Table Question:
I have workbooks i have built and re-use with fresh data after verifying that the previous data has been cleared. Unfortunately there are times in the drop down filter, old data will remain, but is not at all in the data set. How to clear this out? I have ensured that before pasting in new data the old data has been cleared below the headers. I keep my headers so I don't need to re-do the Pivot Tables or adjust the Name / SUM capabilities in the VALUE FIELD SETTINGS.
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec .AutoFilterMode = False .Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd With .AutoFilter.Range On Error Resume Next Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12) On Error GoTo 0 End With If rng Is Nothing Then MsgBox "No data to copy" Else rng.Copy shtLehman.Range("A6") End If .AutoFilterMode = False End With
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z 2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID PQ115 PQ115-Login Bug Description text xihllloloj43712 123 PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
i have soccer table 1 sheet is "teams" which is the table of the league. 1 sheet is "games" which is the table of games of the league the last sheet called "import by date". in the end of every game day i sent email with the results of current day. for now im copy and pate all results and its not that easy and convenient. i want to make some formula that when i click in import data sheet the date its shows me all the results from that date. i try to do it with pivot table but didnt like how it displayd
I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.
Sheet1 A B
[Code]....
Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details
If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.
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
I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.
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?
The task that I have to make is a little bit complicated, especially for reason that I am not v VBA programmer. But however here is the task (and is not a school assignment).
First I have to clear the worksheet Invoice_Plan (file Financialplan.xls), but the whole row(s) that have data in column F (comment) have to stay on worksheet.
Than I have a worksheet Data (file invoices.xls where are all invoices from year 2009) and in column A are the data, that I have to import into in worksheet Invoice_Plan to (second file FinancialPlan.xls) in column A data under following rules (other must be skipped):
there is i website that i want to imoport data from it to my excel sheet using vba, but the poblem is that the website will need my username and password...
i search around for possible threads and i found a very close one but couldnt have it do the submit option. also i donot want it to show me the intenet explorer window. i just wantit to do these steps: after clicking a button:
1-logon to the site using my user and password 2-redirecting to the report link and improt what ever is in that page 3- all this with out opening IE.
Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean
Private Sub LoginTone()
'Dim ie As InternetExplorer 'Set ie = New InternetExplorer Dim ie As Object Set ie = CreateObject("InternetExplorer.Application")
With ie .Navigate "http://s8.brono.com:80/mongol/fiona/index.php" ; this is the login address Do While .busy And .readyState 4: DoEvents: Loop.......................
I want to create a VBA that import data from another workbook. when i run the Macro it give me the option to choose the file and when i select the file then copy data from sheet 1 (A1:D1) and paste to my active worksheet. I do not want the other file open because it is too big and takes fro ever to open it.
I am trying to import data from an external source file (Excel) into an Excel sheet that will get uploaded into a db accounting system.
The source data varies month to month in the number of rows and need I to import 7 columns.
The upload file will be saved as a text file for the upload. I tried to use Import External Data, but since the rows vary in size (anywhere from 1 - 2000 rows) I had to create a named range for the entire column. Upon saving this caused all rows beyond the last row of data to have quotes in them which will kill the upload. Is there a way around this with the Import External Data, or is there a better way through some combination of INDEX, MATCH, INDIRECT or the like?
I am trying to import data from web to excel. I posted the link below to the website I am attempting to import from. The only part I need is the statistics. However, I cannot get it come thru by getting external data from web.
The goal is to great an auto updating spreadsheet with multiple sheets so copy and paste is not feasible.
I am trying to import data from different spreadsheets "about 100" to a principal spreadsheet which will have all the centralized data. I included the principal spreadsheet layout and a sample of two detail spreadsheets. The information that I want to import is from column B 1 to 5 from the detail spreadsheets and put into the principal spreadsheet from row D to G.
Is there any way to import this information automatically, like creating a macro or something similar.
I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually.
