'Get all the sheets
'y = 3
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "OM Files"
For J = 2 To Sheets.Count
With Sheets(J).UsedRange
x = Sheets(J).Name
If x "xxx" Then
.Resize(.Rows.Count).Copy _
Sheets(1).Range("A65536").End(xlUp)(3)
End If
End With
Next
Application.DisplayAlerts = False
I have a spreadsheet that lists values for different grid lines. On top of each set of data, I have labels each line "Line A", etc. I want to put in possibly a pull down menu at the top of the sheet that the user could use to jump to whichever line they were interested in viewing. For example, if the user needs data from Line L, they could select Line L from the drop down menu and the spreadsheet would jump down to that area.
View 7 Replies View RelatedI am trying to write a vba code to change the zero values of the sheet to either 1, -1, or 0 according to certain rules. my sheet looks like this:
chcount=10chcount=10
0000
0000
0000
0000
1111
0000
1111
0000
0000
0000
0000
18111
0000
-1-1-1-1
0000
0000
0000
0000
-16-1-1-1
Basically I need the macro to select clusters of zeros in each of the columns with a label of 10 characters (this is an arbitrary number I chose when writing the macro), and if the values of the cells immediately above and below the cluster are the same, change the value of the zeros to the value of those cells, otherwise, the value of the custers remain as zero. So far I have the following code.
I've attached a sheet with details. I have references that I want to copy to a different cell but I want the reference to stay fixed AND allow the column to move.
Book1.xlsx
I'm trying to calculate the area of a Freeform shape.
Is this already stored somewhere e.g a shape property?
If not is there a macro available to do this?
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
can shut off the automatic calculation of a particluar worksheet or better yet, certain areas of a workbook, leaving the remainder of the workbook as Automatic?
For example, a workbook with Sheets 1,2,3 and 4. Can Excel 2007 be set for automatic calculations on the entire workbook except for Sheet3?
I have a table that lists staff according to where they are located, all adjusted using drop down lists, problem is that some locations have 5 staff and some up to 40, so when I set up a macro to print the area, those with 5 consultants look very small because the page area is so big. how can I adjust the print area dynamically so it only prints to 1 row below the last line with text?
View 9 Replies View RelatedWhen would the AREAS() Worksheet function be used? I've never seen it used and I've been Googling around to no avail.
View 9 Replies View RelatedI frequently receive workbooks containing up to 50 sheets and on most of them there is a selected area.
Is it possible to put a bit a code in the worbook when opening to clear all selected areas on all sheets.
I have an Excel sheet that I want to scroll up and down and I want an area in the upper right-hand corner to display static information. This information could change as more input into the sheet is performed, but it always must remain in the upper right-hand corner.
For example, let's assume the sheet has 200 rows to enter in a credit card application. There would be an area in the upper-right that would display information for some of the cells that were typed in. So as you type information, labels would display information from the form. I know that I can program the selection change event to re-position the box and labels, but if I scroll off the screen, the area does not stay there until I click on a cell on the page. I am using XL2003.
I have a userform that has two comboboxs on it. When the user clicks and selects an item in combobox 1 the code will highlight an area of cells that I set before. When the user clicks and selects an item in the combobox2 I need a piece of code that will highlight a second area of cells while still keeping the first areas of cells highlighted.
How I have it set up now if the combobox1.text = "Monday" then the cells B6:H20 are selected. But when the combox2 click event fires and selects another range of cells the first range selected by the combobox1 event is unselected. Is there a way to keep both areas selected?
I have been conducting seasonal characteristics of the availability of certain fruits and vegetables in the region I am in (Italy), now I want to compare the data from my first seasonal characteristics region (Texas). My goal is to see trends, view characteristics of climate in that given month in an easy view.
I have data for both lists containing over 50 vegetables / fruits each. Along with different side notes that show factors of each month like climate, availability, flavor profiles, and cooking techniques.
Is it possible to put it into format showing a 12 month period, (jan, feb, mar,......) and then putting the type of fruit/vegetable to see trends? along with adding the side notes...
I have locked print areas on 7 work sheets. I have successfully printed them at 1 time. However it appears as if the print areas have to be set again before I print them again? What's up.
View 2 Replies View RelatedI have seven printareas as follows:
$G$8:$Q$40 - Landscape
$C$42:$M$108 - Portrait
$O$42:$Y$108 - Portrait
$C$110:$M$157 - Landscape
$C$159:$M$206 - Landscape
$O$110:$Y157 - Landscape
$O$159:$Y$206 - Landscape
As you can see, when I go to print preview, I will want the first and the last four to be formatted landscape and the second and third to be formatted portrait. This is all I want the macro to do. Then later when I go to print, I want to be able to print them all together. Is there a way to do this? Perhaps by letting the first printarea be on its on sheet, the second two on their own sheet, and the final four on their own sheet?
i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.
View 13 Replies View RelatedWhat is the benifit of the worksheet function AREAS?
How can we use it with other functions?
In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..
I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.
My code is below. See how I have repeated the same code over and over for each row. Is there a shorter way to write this, perhaps a cleaner way. Im not an expert and I write what I know and most of that is long code that can be shortened.
If MyScreen.Area(7, 2, 7, 4) = "102" Then
Workbooks(2).Worksheets(1). Range("B" & row) = "Yes"
ElseIf MyScreen.Area(8, 2, 8, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(9, 2, 9, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(10, 2, 10, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(11, 2, 11, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(12, 2, 12, 4) = "102" Then .....................
I am attempting to set a range of cells (say c5:h25) that can be scrolled through horizontally whilst the rest of the sheet remains frozen.
I'm trying to set this so the scrollable range will automatically expand horizontally as user data is added to the last (h etc) column.
I am trying to iterate through ranges (F2:G4) , (F5:G7) , ( F8:G11) , ... , lastrange
With each range I perform a simple median calculation.
The output of each calculation I would like to place in H2 , H3 , H4 , ... , lastcalculation
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage
Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
Employee Productivity.xls
I produce spreadsheets which is conditionally formatted to be both functional and aesthetically pleasing. The tables are separated by one line, and can easily be defined by VBA, I was wondering if it is be possible to export them as image files via vba? I know it can be done for graphs, but not sure about defined sections of the worksheet?
View 1 Replies View RelatedI have a situation which I hope is fixable. I have frozen panes above and to the left of cell F5, therefore all columns to the right of column E scroll left and right and all rows below row 4 scroll up and down as required. My problem is that I also need the whole top row to remain static all the time.
View 3 Replies View RelatedI have a column of the date of sales. (several hundred lines long) and each line has the name of the item sold. I need to complete a report showing how many sales of each item per date. Date of sale in one column and the name of the product in another.
View 3 Replies View Relatedi am making a spreadsheet for recording employee telesales activity each day.
administrators will update a shared workbook each day with the information
employee names are across the top and the activities are down the side (calls, CVs sent, interviews, meetings) each day has it's own block of data. on a friday I would like to run a macro to select these blocks and paste them into the first free row on a separate worksheet.
i have something similar working elsewhere which copies one column and pastes that into the last free column of a new sheet but i have no idea how to do it for rows going down a page. I have attached a file example to clarify.
I'm just stuck on a final thing......I would like the pivot table to collapse based on the outcome of a formula. For instance I've got a project that has A, B, C, D as component parts, these parts all have a percentage work completed. If let's say A is a 100% complete I would like to automatically collapse A, so it will just show the summary for A. Obviously this can be done by hand, but since there is quite a lot of data I would like to automate the proces.
View 8 Replies View Relatedi have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedIs it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.
View 1 Replies View Related