Link: Create a workbook from every worksheet in your workbook
It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?
I'm using Excel 2003.
Intend creating a calculator that shows me how much a certain hotel room costs at different times (seasons) of the year, for example I have a One Bedroom Budget Apartment (list of room types is extensive), I also have three seasons, low, S Hols and Xmas, so I have a sheet created and inserted drop down boxes for the RoomType, Season, and Number of days, I now have to insert a formula that looks for the three variables and inserts the cost (I already have this info on a worksheet)
TotalCost
RoomTypes
Season
Days
[Code].....
Also I am using Excel 2003 so no combo box option (that I can find anyway)
Writing a VB macro to automatically export multiple worksheets to one large or many individual comma delimited CSV files. There could be as many as 100 worksheet tabs that are virtually identical each with historical stock price data....a different stock ticker for each tab.
Here are a couple of links that seem relevant: [URL]
I need a macro to export a range of cells to .html. The Save as Web Page works fine for me except a few thing that I need to automate. The macro I use now looks like this (recorded):
View 3 Replies View RelatedI would like to merge several sheets into one sheet, all from within the same workbook. All sheets have the same data layout. I use xl 2003.
How can I merge them all into a single worksheet?
I created the following in Excel 2007 but need to come up with a replacement formula in 2003.
=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")
With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.
In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
View 5 Replies View RelatedI some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.
I'm using Excel 2003.
Row totals exceed the limit for Excel 2003, so I have split the data into different worksheets by year.
Problem is, now I can't create a useful pivot table, i.e. using 'multiple consolidation ranges' reduces the field list to "Row", "Column", and "Value".
I have 26 columns in each worksheet (all identical structures); some are multilevel factors, some are variables - and the way these relate to each other is unknown as this stage, so I literally cannot have the dimensions reduced in the way Excel proposes with multiple consolidation ranges.
(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.
View 2 Replies View RelatedI have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.
Sheet1 sample:
Type
Selection
[Code]...
The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).
If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.
So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.
Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.
I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!
I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.
So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.
Sample of Sheet1
Country
Scotland
Location
Glasgow
Start Date (dd/mm/yyyy)
30/04/2013
[code]....
So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.
I need to create a function which will take a objectname (a query or table) and a file name format
The objective is to export this query or table into multiple text files each with a maximum of 1000 lines per text file. These files are used as an input for SAP - the interface only takes a maximum of 1000 lines per file.
I am having three issues with the code below.
1. How do I split an excel query based on line numbers? Is there a way to get the equivalent of ROW_NUM in oracle ? Or do I have to loop through it and maintain a counter?
2. What is the best way to split the file ? Loop within loop? I need the files to be tab separated (no header required)
3.The rst.RecordCount is acting strange - when I pass a name of the query - the property returns the right no. of rows, however when I pass a any table name it only returns 1 - is this the expected behavior - or am I missing something
Code:
Function ExportAsText(strObjectName As String, strFileName As String) As Long
' Purpose: Export any given query, table to mutliple files each of certain length
' the no. of lines per text file is defined by the config parameter SAP UPLOAD, LINE LIMIT PER TEXT FILE
' in the LKUP_CONFIG database
Dim db As DAO.Database 'This database
[Code] ..........
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
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 table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.
I use Excel 2003 so SUMIFS don't work.
How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.
Start Date
11/1/2012
End Date
11/31/2012
Assigned To
Created
Fixed
[Code] .....
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs
View 3 Replies View RelatedI am currently using Excel 2003. I have a worksheet with two tabs.
First tab has a list of bank Names. Second list has Bank Names and balances.
I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.
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 have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.
So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.
Windows XP
Excel 2003
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
I am using the following formula:
=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
Using Excel 2003
I would like to add hyperlink to multiple JPEG images (separately) in Excel 2003 and send those images via email to another person. On receiving the mail at the opposite end, the person concerned should be able to view the images by clicking the link. How to do?
View 1 Replies View RelatedI have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.
Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
I am using Excel 2003. I have a spread sheet and I am trying to use conditional formatting. But I can't figure out how to use conditional formatting across multiple columns and so I have been doing each column individually which has been kind of a hassle. I also am trying to figure out how to put in a formula so that every occurrence of a certain text will show up a certain color. I know that I can just do "cell value is", "equal to" and click on the cell, but the information in this spread sheet is organized alphabetically and is going to be added to frequently therefore every time I add information it re-organizes. This then messes up my conditional formatting because the text value that was in the cell gets bumped up or down.
View 2 Replies View RelatedI have a sheet (sheet 1) of employee information for multiple buildings as follows:
Employee Name, Building, Pay Grade, Full Time Equivalent (of full time hours worked)
Anne, Centre A, Anne, Grade 2, 1.0
Bob, Centre E, Bob, Grade 3, 0.50
Carl, Centre H, Carl, Grade 2, 0.60
Dan, Centre B, Dan, Grade 1, 1.0
Emma, Centre A, Emma, Grade 3, 1.0
Fash, Centre A, Fash, Grade 2, 0.40
Gemma, Centre C, Gemma, Grade 2, 1.0
Hanna, Centre B, Hanna, Grade 1, 1.0
etc, etc, etc
From another sheet (sheet 2), I wish to populate the total of "Full Time Equililents" at each grade in each centre, eg:
CENTRE, GRADE 1, GRADE 2, GRADE 3
Centre A, 0 1.4 1.0
Centre B, 2.0 0 0
etc,etc,etc
I'm using Excel 2003 if that makes a difference
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View Related
