Excel 2002 :: Setting Variable To Select All Worksheets In Workbook
Jan 20, 2012
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.
I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.
[Code] .....
The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.
I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.
I am trying to send emails using a table in Xl2002 via outlook2002.
The code below is from Walkenbach's VBA Bible for Excel 2003 and I am getting "Uesr-type not defined" error with the 1st Dim statement.
Do I need a different statement such as set OutObj = blahblah or similar?? I have searche dthe net hi & lo for a solution to this problem & I do have JW's excel VBA bible.
Sub SendEmail() 'Uses early binding 'Requires a reference to the Outlook Object Library Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim cell As Range Dim Subj As String Dim EmailAddr As String Dim Recipient As String Dim Bonus As String Dim Msg As String
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells: $DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
The formula is working well. The problem is I get the result for the first column of error even if the error is in more than one column. I want the result should display all the errors with the column number 1, 2, 3 (or with column name A, B, C etc). The total number of error is not required. The exact columns which have the error should be displayed. For example if the error exists in 3, 4 and 5 columns, I should get the result as "345Error" or "CDEError".I use excel 2002.
I've got Excel 2002. I am devising a sheet for checking work (sheet A) which feeds onto a separate sheet (sheet B). If I wanted to delete info from sheet A can I retain the info on sheet B? I want sheet A to be used repeatedly for each case but the info for every case to be recorded row by row on sheet B.
I'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
Excel 2002.I have data in columns A and B on sheet 3, starting at row 100. The total number of rows of data is variable (max is 50 rows). I want to take the values in A100:B100 and copy them from Sheet3 and paste values transposed to SheetTL Range C1:C2. I have started to write the following code but as you can see I will have to repeat the code upwards of 50 times. It seems I should be able to do a COUNT function to see how many rows of data exist and then somehow loop the code to copy the data from each subsequent row that many times, but how to put that together.
Sheets("Sheet3").Range("A100:B100").Copy Sheets("TL").Range("C1").PasteSpecial Paste:=xlPasteValues, Transpose:=True ActiveSheet.PrintOut If Sheets("Sheet3").Range("A101") = "" Then
I have a workbook in which there is a sheet containing a table.
Excel 2002ABC123Specific Gravity6 Volt ValuesSOC40.0000.000Dead 51.1345.97010%61.1485.99020%71.1626.02030%81.1766.06040%91.1906.10050%101.1976.12055% 111.2046.14060%121.2116.16065%131.2186.18070%141.2256.20075%151.2336.23080%161.2416.26085% 171.2496.31290%181.2576.32095%191.2656.350100%SOC Chart
From another sheet I query this table using the LOOKUP function to obtain values from column C such as 25% 60% and so on. A typical query formula looks like this:
=LOOKUP(B2,'SOC Chart'!B4:'SOC Chart'!C19)
It would be nice if the fill color came along with the value.
I found in an old file that i triggered the macro by clicking a button! I would post a pic but i don't know how. How do I activate a macro by clicking a shape on the excel 2002 page?
Basically, I have an excel spreadsheet with different valued items for example,
TV Essential l £15 TV Essential extra l £20
Broadband Essential l £ 8 Broadband Extra l £12
I have options for TV, Broadband and Phone. At the bottom of the spreadsheet I want three drop down lists to pick what TV, Broadband and Phone package I want. For example: TV Essential, Broadband Extra and Phone Weekend. I can create the lists but I was wondering if there was anyway where, as you select the option on each of the lists, a running total will be created. So when TV Essential is selected £15 is added to a cell, say F20. Then when Broadband Extra is selected another £12 is added to cell F20. And as you select different ones in each list the total in F20 changes.
If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…
i.e.
spl1 spl2 spl3 . . . splx
x=number of samples
This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
I have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"
I am attempting to set a variable as a range of data that sits on a non-activated worksheet. However, I am getting a runtime error of 13 (data mismatch).
Is there something in my code that may be incorrect? Here is my code:
Dim rRange As Range Dim sRange As String Dim tblName As String
I'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.
I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.
Code: Dim aWB As Workbook Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String CurrentRange = Range("AG3").Value MsgBox (CurrentRange) 'This returns the value [N7:N75], which is what I want Set UtilizationRange = Range("CurrentRange")End Sub
i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.
Option Explicit Dim rng As Range Const FirstRow As Long = 2 Dim r As Long Dim ans As Variant Dim MyArray(100, 4)
I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time. that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64
Code that will autofilter my sheet for rows that contain either a 1,2,3, or 4 in Column "Z". Then, create a new workbook for each unique value in column "O" and save that to a folder on my desktop named "Contracts". Next, while still filtered by by "Z" and for every unique value in column "O", filter and name a sheet in the new workbook for each unique value in column "B". Finally, while the main sheet is filtered I need to copy and paste the visible cells (minus header row) from columns C,D,E,F,G,H,S, and U to consecutive columns in the newly created worksheet beginning in cell "A9".
I am using excel to create some contracts for a few partners. Each partner could work with several of my clients and I need to give each partner a personalized contract for each of my clients with which they work.
I hope my description of the problem is not too confusing. If so, let me know and I will try to clarify exactly what I am trying to say.
I have tried to combine some code that I already have but, being a newb with loops, I can't seem to get it to do everything.
