i want to import data from multiple excel files (with same kind of data) into a master file with each import want to display name of the file from which the data is imported in last column on all rows. for example if file 1 has 10 rows with file name abs, after import the macro show display on all 10 rows in a blank column abc. then import second file and its name and so on.
View 3 RepliesNeed code to import a worksheet (there is only one) from another workbook. But I just get gibberish when I import it. Both files are .xlsx files.
Here is my code below. I used this to import a CSV file. I don't know if I can't use this to import excel files, but it just doesn't seem to work for me.
Code:
Sub load_ClientCustomerData()
dim fStr as string
With Application.FileDialog(msoFileDialogFilePicker)
[Code].....
I want to import an excel file that has multple columns, however, the first column contains both the Group name and the dates. Is there a way to import this into a table to have each record populate the group to the corresponding date?
BASE Researching Duplicate
11/23/2011 21
E-Team General Pool
11/23/20112120
11/25/20112318
General Pool for SB Activations
11/23/2011429418
11/24/2011251125
11/25/2011485452
11/26/2011452410
11/27/2011415408
I have a folder in my D drive where I used to keep my Text files after downloading it from our support centeral. I want to Import all these Text files in one excel sheet in a way that data of each file got append in excel sheet below previous file data.I am trying to do this through FSO but unable to do the same.
View 1 Replies View RelatedI would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
I am importing some data from a website into several sheets but when I refresh it I am not able to access the data as numbers. I am using a sumif to collect the data. See the attached sheet. if there is a way to do this without sumif, that would work as well, but my numbers are not copying over to the main sheet because they are not being imported as numbers. also when the a and c are changed on the main sheet i want the values to change with them.
View 2 Replies View RelatedI have a workbook called summary with a sheet called "detail" I have 4 workbooks Called week 1, week 2, week 3 and week 4. All the week workbooks have a sheet called Summary. I need to import the summary sheets from each week workbook into the the detail sheet. All workbooks are in the same folder. I need to only copy columns A,D,F,G.
If someone can post code or point me towards a thread I can figure what changes need to be made. I am getting better but slowly!
I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.
View 14 Replies View RelatedI am in process of making an appraisal system for my organization through excel templates .Each employee will Have 2 KRA templates .One specific to their roles and other in the area of their interest.
So while rating i will select role of the person and his interest area.Then the comprehensive template integrating both templates should be generated from the master templates which are in place.If i make any edits in Integrated template,it should not be reflected in master templates
I am doing a project with these instructions:
The state, capital and population of all states with a population greater than 10 million or less than 1 million. (population)
The state, tree and bird of all states with a bird or tree that starts with Eastern or Western (symbols)
The state, population and area of all states with area between 100,000 and 200,000 (area)
I am having troubles with selecting just the three criteria (state, capital, and population) on my Population worksheet. Attached is my work. The copied filter keeps copying all of the data in the data set, and not those three.
I have a workbook with multiple sheets (21 sheets) in it. The header on each sheet begins at row B5. The actual data starts from row B6. The number of rows in each sheet varies and the last row contains the total for all columns that has numbers. I wish to have one sheet that combines data from all the 21 sheets. But;
1) The header should be repeated only once
2) Only the rows that has data should be included, blank rows should be ignored
3) I do not need the last row i.e. total to be considered while merging the sheets
I've given a snapshot of the data below. The first column contains the row number just for reference.
B5 Name Address Telephone Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Total Other Members B6 Tony PQR Complex Road 1 12345678
300
500
800
B7 Stany PQR Complex Road 1 1234567890
1,000
[Code]...
[IMG]file:///C:/Users/DERICK%7E1.FER/AppData/Local/Temp/moz-screenshot.png[/IMG]
Currently, I have 65536 rows of data per sheet in an excel file. I have a total of 8 sheets. I need to combine all these sheets into one csv file. As my company uses excel 2003, I can't consolidate all sheets into 1 sheet before saving as CSV format as it will exceed the number of rows available in excel 2003.
However, the number of rows and number of sheets in the excel file will change monthly, and I need to consolidate the excel file accordingly.
I am wondering if I could save a new workbook as csv format first before copy and paste all data into that csv file.
Also, all the 8 tabs have the same header. I only need to copy the header on the first tab, and for the sequence tab, I will only need to copy row 2 onwards.
I have recorded a macro based on a sample data.
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settingse31425My DocumentsBook1.csv", _
FileFormat:=xlCSV, CreateBackup:=False
[Code] .......
I have some 400+ .txt tab delimit files in the same format in the same directory. All of them have 4 cols. I would like to use a VB sub code to import col1's of all .txt files to a workbook sheet1 filing from col1 to coln in excel sheet1. And col2's of all .txtx files to sheet2 of the same workbook. An so on. I found a code that can import the col1's from .txt files to excel sheet1 only. But not col2's to sheet2.
View 6 Replies View Relatedhow to extract data in the following case: I have the following columns:
Employment Name
code1
code2
cost1
[Code]....
how can I do this using formula ? For one sheet and for extracting this data from multiple sheets into a new one in the same workbook?
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
I am using Excel 2007. I'd like to merge multiple sheets (about 13) into one workbook. The sheets are placed in one folder, and they all include 2 sheets, - only the first sheet should be merged into the final workbook.
The sheets will be updated every 3 months and merged again (-thus replacing the old data).
I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
Writing code in VBA for splitting data into multiple sheets?
The split needs to be based on value in one of the columns and the sheet where the data will be sent to should get sheet name same as value in the column.
Following is the input sheet, the data needs to be split based on entry in the column "Job Type".
Excel 2007ABCD1Invoice NumberJob TypeInvoice DateAmount21234Repair
04-Apr-13$20033456Contract Work10-Apr-13$40047896General23-Apr-13$100Input Sheet
Following tables are showing data split into multiple sheets and the sheet names are the same as entry under the column "Job Type"
Excel 2007ABCD1Invoice NumberJob TypeInvoice DateAmount21234Repair04-Apr-13$200Repair
Excel 2007ABCD1Invoice NumberJob TypeInvoice DateAmount23456Contract Work10-Apr-13$400Contract Work
Excel 2007ABCD1Invoice NumberJob TypeInvoice DateAmount27896General23-Apr-13$100General
As part of report consolidation I need a Macro that can read through all the files in a particular location(Say a folder path) Pull the data for each unique products into separate sheet
In the example below : All the rows with "Chocolates to sheet 1" , Drink to Sheet 2 and so on Since the products in column 3 can vary the unique sheets need to be created at run time based on the source data
Workbook1
WEST
100
Chocolates
[Code]....
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
I want to copy 4 sheets and paste it in a new workbook and save it.
I have this code recorded
VB:
Sheets(Array("PIV", "Report")).Select
Sheets(Array("PIV", "Report")).Copy
But it don't work?
I have a workbook that contains something like 50 worksheets and they all contain data with the same columns, for example column A is Project, column B is Project Name, etc. I need to convert data in each worksheet into an Excel Table. There has to be an easier way than manually converting each worksheet into a Table. However, when I group all of the sheets, the option to Insert a Table is not available even though the data starts in the same row and contains the same number of active columns in each worksheet. Is there an easier waty to insert a table in all of the worksheet simultaneously?
View 2 Replies View RelatedI'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies View RelatedI have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.
The code I am using is:
Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub
In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
I am working on a "3 worksheet" excel workbook. The first worksheet does not require any header.
I'd like to enter data into the second sheet (say cells A1 and B1), and use VBA to pull from those cells to generate the same custom header for both the second and third worksheets.
For example, I'd like the header to pull "# 123456" from cell A1, and "789" from B1 in sheet two, putting them in a centered header for both sheets two and three (same reference cells from sheet two for both, not new values of A1 and B1 from sheet 3 for sheet 3 header). I'd like to format in a way that looks something like this:
#123456
789
I'm currently running Excel 07, and was able to pull from a cell on one worksheet into that sheet's header but couldn't get it to span multiple sheets.
I have an annual leave (vacation) work book with a summary page work sheet and separate work sheets for each month i.e. Jan, Feb, Mar.....to......Dec. In order to protect the formulas I have protected them by allowing only access to the input cells on each work sheet and the protect each work sheet.
When someone either joins or leaves the team I have to manually unprotect each sheet and protect again when I have completed the amendments to each of the 13 tabs.
macro code I would need to unprotect all the works sheets in one go (as I use the same password for all the sheets) and reset the passwords (protect) the sheets with more macro code.
I will be running the two macros from my own personal.xls file and ideally they would be fully automatic i.e. I would not need to input the passwords in to unlock or lock the work sheets as the password would be written in the code already
Excel version 2003
my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. I'm at the point where it all works fine until it hits Consolidate.
Option Explicit
Sub ConsolidateExport()
'
' ConsolidateExport Macro
[Code]....